Last Date Closing balance is not carry forward against same date?

Question

I have facing issue, when i am applying date filter,after same date opening balance is not carry forward ,

below is data

Create table #Tbl_Customer (C_ID int,C_Name varchar(50),Opening_Date date,Opening_value decimal(10,2))

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','2023-09-30',2500)
insert into #Tbl_Customer values (1002,'Noman',null,null)


Insert into #tbl_Ledger values (7,'2023-09-05',1001,1002,4000) 
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 (14,'2023-10-03',1002,1001,1500) 
Insert into #tbl_Ledger values (5,'2023-10-04',1002,1001,3000) 
Insert into #tbl_Ledger values (6,'2023-10-04',1001,1002,6000) 
Insert into #tbl_Ledger values (16,'2023-10-09',1002,1001,3000) 
Insert into #tbl_Ledger values (12,'2023-10-15',1002,1001,2000) 
Insert into #tbl_Ledger values (13,'2023-10-18',1001,1002,2500) 

I am pass date parameter from '2023-10-03' to '2023-10-20',it is giving correct Result of balance

declare @C_ID as int = 1001
declare @start as date = '2023-10-03'
declare @end as date = '2023-10-20'


; WITH Unio AS (
  SELECT NULL AS T_ID, Opening_Date AS E_Date, 
         IIF(Opening_value > 0, Opening_value, 0) AS Debit,
         IIF(Opening_value < 0, Opening_value, 0) AS Credit,
         Opening_value AS Amount
  FROM   #Tbl_Customer
  WHERE  C_ID = @C_ID
  UNION ALL
  SELECT ID, E_Date, IIF(Debit_Head_ID = @C_ID, T_Amount, 0),
                     IIF(Credit_Head_ID = @C_ID, T_Amount, 0),
         CASE WHEN Debit_Head_ID = @C_ID THEN T_Amount 
              WHEN Credit_Head_ID = @C_ID THEN -1 * T_Amount 
         END
  FROM   #tbl_Ledger
  WHERE  E_Date > (SELECT Opening_Date FROM #Tbl_Customer WHERE C_ID = @C_ID) or E_Date>'2023-10-01'
), 
runsum AS (
   SELECT T_ID, E_Date, Debit, Credit,
          SUM(Amount) OVER(ORDER BY E_Date 
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
						   ) AS Balance
   FROM  Unio
), 
prevbal AS (

   select   Top(1)BALANCE prevbal from runsum
	where E_Date<@start
	Order by E_Date desc

)
SELECT NULL AS ID, NULL AS E_Date, 'Opening' AS Remarks, 
       NULL AS Debit, NULL AS Credit, prevbal AS Balance
FROM   prevbal


UNION ALL
SELECT T_ID, E_Date, '' AS Remarks, Debit, Credit, Balance
FROM   runsum
WHERE  E_Date BETWEEN @start AND @end
ORDER BY E_Date asc
	Drop Table #tbl_Ledger
		Drop Table #Tbl_Customer

But i want i pass date parameter from '2023-10-04' to '2023-10-20',then last balance of date 2023-10-03 with last T_ID,is not coming as i highlighted in red which is incorrect,Green highlighted balance is correct

declare @C_ID as int = 1001
declare @start as date = '2023-10-04'
declare @end as date = '2023-10-20'


; WITH Unio AS (
  SELECT NULL AS T_ID, Opening_Date AS E_Date, 
         IIF(Opening_value > 0, Opening_value, 0) AS Debit,
         IIF(Opening_value < 0, Opening_value, 0) AS Credit,
         Opening_value AS Amount
  FROM   #Tbl_Customer
  WHERE  C_ID = @C_ID
  UNION ALL
  SELECT ID, E_Date, IIF(Debit_Head_ID = @C_ID, T_Amount, 0),
                     IIF(Credit_Head_ID = @C_ID, T_Amount, 0),
         CASE WHEN Debit_Head_ID = @C_ID THEN T_Amount 
              WHEN Credit_Head_ID = @C_ID THEN -1 * T_Amount 
         END
  FROM   #tbl_Ledger
  WHERE  E_Date > (SELECT Opening_Date FROM #Tbl_Customer WHERE C_ID = @C_ID) or E_Date>'2023-10-01'
), 
runsum AS (
   SELECT T_ID, E_Date, Debit, Credit,
          SUM(Amount) OVER(ORDER BY E_Date 
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
						   ) AS Balance
   FROM  Unio
), 
prevbal AS (

   select   Top(1)BALANCE prevbal from runsum
	where E_Date<@start
	Order by E_Date desc

)
SELECT NULL AS ID, NULL AS E_Date, 'Opening' AS Remarks, 
       NULL AS Debit, NULL AS Credit, prevbal AS Balance
FROM   prevbal


UNION ALL
SELECT T_ID, E_Date, '' AS Remarks, Debit, Credit, Balance
FROM   runsum
WHERE  E_Date BETWEEN @start AND @end
ORDER BY E_Date asc
	Drop Table #tbl_Ledger
		Drop Table #Tbl_Customer

Answer 1

Hi @Analyst_SQL

I tweaked the script a bit, you can try it.

declare @C_ID as int = 1001
declare @start as date = '2023-10-04'
declare @end as date = '2023-10-20'


; WITH Unio AS (
  SELECT NULL AS T_ID, Opening_Date AS E_Date, 
         IIF(Opening_value > 0, Opening_value, 0) AS Debit,
         IIF(Opening_value < 0, Opening_value, 0) AS Credit,
         Opening_value AS Amount
  FROM   #Tbl_Customer
  WHERE  C_ID = @C_ID
  UNION ALL
  SELECT ID, E_Date, IIF(Debit_Head_ID = @C_ID, T_Amount, 0),
                     IIF(Credit_Head_ID = @C_ID, T_Amount, 0),
         CASE WHEN Debit_Head_ID = @C_ID THEN T_Amount 
              WHEN Credit_Head_ID = @C_ID THEN -1 * T_Amount 
         END
  FROM   #tbl_Ledger
  WHERE  E_Date > (SELECT Opening_Date FROM #Tbl_Customer WHERE C_ID = @C_ID) or E_Date>'2023-10-01'
), 
runsum AS (
   SELECT T_ID, E_Date, Debit, Credit,
          SUM(Amount) OVER(ORDER BY E_Date 
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
						   ) AS Balance
   FROM  Unio
), 
prevbal AS (

   select   Top(1)BALANCE prevbal from runsum
	where E_Date<@start
	Order by E_Date desc,T_ID desc

)
SELECT NULL AS ID, NULL AS E_Date, 'Opening' AS Remarks, 
       NULL AS Debit, NULL AS Credit, prevbal AS Balance
FROM   prevbal


UNION ALL
SELECT T_ID, E_Date, '' AS Remarks, Debit, Credit, Balance
FROM   runsum
WHERE  E_Date BETWEEN @start AND @end
ORDER BY E_Date asc
	

Output:

Best regards,

Percy Tang