Debit and Credit with Running Balance with Date filter

Question

When i applying date between filter ,the last date balance must be opening in Balance ,but below query is not giving correct ouput.

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) 


;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
where E_Date  between '2023-10-03' and '2023-10-04'

Drop table #Tbl_Customer
Drop table #tbl_Ledger

as you may see in image ,Output must be as below

Answer 1

Try something like this:

declare @name as varchar(max) = 'Akhter'
declare @start as date = '2023-10-03'
declare @end as date = '2023-10-04'

; with Q1 as
(
    select t.ID, t.E_Date,
        case c.c_ID when Debit_Head_ID then T_Amount else 0 end as Debit,
        case c.c_ID when Credit_Head_ID then T_Amount else 0 end as Credit
    from #tbl_Ledger t
    inner join #Tbl_Customer c on c.C_Name = @name
),
Q2 as
(
    select ID, E_Date, Debit, Credit, sum(Debit - Credit) over (order by E_Date, ID) as Balance
    from Q1
),
B as 
(
    select top(1) Balance 
    from Q2
    where E_Date < @start 
    order by E_Date desc, ID desc
),
R as
(
    select ID, E_Date, '' as Remarks, Debit as Debit, Credit as Credit, Balance, 2 as g
    from Q2
    where E_Date between @start and @end
    union all
    select NULL, NULL, 'Opening' as Remarks, NULL, NULL, Balance, 1 as g
    from B
)
select ID, E_Date, Remarks, Debit, Credit, Balance
from R
order by g, E_Date, ID

Answer 2

The reason it's appearing incorrect is because you are applying your SUM() after you have applied a filter with your date range. This ignores the opening balance. I made a very minor change that includes another CTE that applies the sum to your entire result set, before you return it:

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) 


;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
), CTE2 AS -- Added CTE2 for SUM() across entire dataset
(
	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
)
SELECT * FROM CTE2
WHERE E_Date  between '2023-10-03' and '2023-10-04'

Drop table #Tbl_Customer
Drop table #tbl_Ledger