Debit and Credit with Running Balance

Question

I want Ledger Report of Customer 1001 'Akhter' with below Data

Create table #Tbl_Customer (C_ID int,C_Name varchar(50))

Create table #tbl_Ledger (ID int,E_Date date,Debit_Head_ID int,Credit_Head_ID int,T_Amount Decimal(10,2) )


insert into #Tbl_Customer values (1001,'Akhter')
insert into #Tbl_Customer values (1002,'Noman')

Insert into #tbl_Ledger values (1,'2023-10-01',1001,1002,4000) 
Insert into #tbl_Ledger values (2,'2023-10-02',1001,1002,1000) 
Insert into #tbl_Ledger values (3,'2023-10-03',1002,1001,1000) 
Insert into #tbl_Ledger values (4,'2023-10-03',1002,1001,3000) 
Insert into #tbl_Ledger values (5,'2023-10-04',1002,1001,3000) 
Insert into #tbl_Ledger values (6,'2023-10-04',1001,1002,6000) 

Output

Answer 1

Hi @Analyst_SQL

Try this query:

;WITH CTE AS
(
SELECT ID,E_Date
      ,CASE WHEN Debit_Head_ID=1001 THEN T_Amount ELSE 0 END AS Debit
	  ,CASE WHEN Credit_Head_ID=1001 THEN T_Amount ELSE 0 END AS Credit
FROM #tbl_Ledger
)
SELECT ID,E_Date,Debit,Credit,SUM(Debit-Credit)OVER(ORDER BY E_Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS Balance
FROM CTE

Best regards,

Cosmog Hong