Debit and Credit with Running Balance with Date filter

Analyst_SQL 3,551 Reputation points
2023-10-06T11:32:24.49+00:00

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

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,925 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,831 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,470 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. Viorel 113.2K Reputation points
    2023-10-06T13:58:44.05+00:00

    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
    
    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. GlockByte 161 Reputation points
    2023-10-06T14:37:27.05+00:00

    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
    
    1 person found this answer helpful.
    0 comments No comments