asked
Calculating Balance using sql
Hi i am using below query to get balance for a certain customer SELECT t.[InvoiceID], t.S_Type as Type, t.Date, t.Debit, t.Credit, b.Balance FROM Statement as t CROSS apply (SELECT Balance = SUM(Debit) - SUM(Credit) FROM Statement as x WHERE (x.date < t.date or x.date = t.date ) AND x.CustID = t.CustID ) b WHERE t.CustID ='1' and date between '2015-01-01' and '2016-01-12' order by InvoiceID, Type desc, Date out put InvoiceID Type Date Debit Credit Balance 3 Sales Invoice 2015-06-09 200.00 0.00 225.00 3 Receipt Voucher 2016-01-04 0.00 200.00 0.00 5 Sales Invoice 2015-06-09 25.00 0.00 225.00 5 Receipt Voucher 2016-01-04 0.00 25.00 0.00 i am looking to get balance in below format is supposed to be InvoiceID Type Date Debit Credit Balance 3 Sales Invoice 2015-06-09 200.00 0.00 200.00 3 Receipt Voucher 2016-01-04 0.00 200.00 0.00 5 Sales Invoice 2015-06-09 25.00 0.00 25.00 5 Receipt Voucher 2016-01-04 0.00 25.00 0.00 when i changed x.CustID = t.CustID to x.InvoiceID = t.InvoiceID i tried with another customer that has all sales invoices the balance give the same debit values it dose not sum the debit values InvoiceID Type Date Debit Credit Balance 1 Sales Invoice 2015-06-09 200.00 0.00 200.00 10 Sales Invoice 2015-06-09 850.00 0.00 850.00 12 Sales Invoice 2015-06-09 20.00 0.00 20.00 59 Sales Invoice 2015-09-03 0.00 0.00 0.00
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
Do you have a unique identifier on the Statement table? If so you can use this to limit the calculation to 'prior' items if object_id('tempdb..#Statement','U') is not null drop table #Statement create table #Statement ( StatementID int identity(1,1), InvoiceId int, custid int, S_Type varchar(50), [Date] date, Debit decimal(8,2), Credit decimal(8,2) ) insert into #Statement select 3,1,'Sales Invoice', '9 jun 2015', 200,0 insert into #Statement select 3,1,'Receipt Voucher', '4 jan 2015', 0,200 insert into #Statement select 5,1,'Sales Invoice', '9 jun 2015', 25,0 insert into #Statement select 5,1,'Receipt Voucher', '4 jan 2015', 0,25 SELECT t.StatementID, t.custid, t.[InvoiceID], t.S_Type as Type, t.Date, t.Debit, t.Credit, b.Balance FROM Statement as t CROSS apply (SELECT Balance = SUM(Debit) - SUM(Credit) FROM Statement as x WHERE x.CustID = t.CustID and x.StatementID
Hi.. We can try this method also using coalesce function. CREATE TABLE Customer ( CustID INT PRIMARY KEY, CustName VARCHAR(20) ) CREATE TABLE CustomersTransactions ( TranID INT PRIMARY KEY, CustomerName VARCHAR(20), TranDate DATETIME, DebitValue FLOAT, CreditValue FLOAT ) INSERT INTO [Customer] VALUES (100, 'Ahmed'); INSERT INTO [Customer] VALUES (300, 'Hana'); INSERT INTO [Customer] VALUES (900, 'Layla'); SET DATEFORMAT dmy; INSERT INTO [CustomersTransactions] VALUES (1, 300, '15/03/2012', 1120, 0); INSERT INTO [CustomersTransactions] VALUES (2, 900, '15/03/2012', 0, 2000); INSERT INTO [CustomersTransactions] VALUES (3, 100, '17/03/2012', 0, 2700); INSERT INTO [CustomersTransactions] VALUES (4, 100, '17/03/2012', 6230, 0); INSERT INTO [CustomersTransactions] VALUES (5, 300, '17/03/2012', 2080, 0); INSERT INTO [CustomersTransactions] VALUES (6, 300, '18/03/2012', 4250, 0); SELECT [A].TranID, [A].CustomerName, [C].CustName, [A].TranDate, [A].DebitValue, [A].CreditValue, (SELECT SUM(COALESCE(DebitValue,0) - COALESCE(CreditValue,0)) FROM dbo.CustomersTransactions AS [B] WHERE [B].TranID <= [A].TranID AND [B].CustomerName = [A].CustomerName) AS 'Running Balance' FROM dbo.CustomersTransactions AS [A] INNER JOIN dbo.Customer AS [C] ON [A].CustomerName = [C].CustID ORDER BY CustomerName, TranID;
20 People are following this question.
