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

Analyst_SQL 3,551 Reputation points
2023-10-13T06:47:28.4433333+00:00

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


User's image

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

User's image

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,871 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,822 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,464 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,249 questions
0 comments No comments
{count} votes

Accepted answer
  1. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-10-13T07:28:34.9833333+00:00

    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:

    User's image

    Best regards,

    Percy Tang


0 additional answers

Sort by: Most helpful