Debit and Credit with Running Balance with Customer Opening Value

Analyst_SQL 3,551 Reputation points
2023-10-07T05:45:28.98+00:00

My requirement, if Opening_Date exists in #Tbl_Customer. If it does,

it uses the "Opening_value" transaction for the specified date range from #Tbl_Customer,

then it will proceed after picking opening_value ,then into #tbl_ledger afterward opening_date .

If Opening_Date is not present,it proceeds with the transaction data from #tbl_Ledger .

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')


Insert into #tbl_Ledger values (7,'2023-09-01',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 (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) 


declare @C_ID as int = 1001
declare @start as date = '2023-10-02'
declare @end as date = '2023-10-15'

; 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_ID = @C_ID
),
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

Drop table #Tbl_Customer
Drop table #tbl_Ledger


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-01',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 (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) 


declare @C_ID as int = 1001
declare @start as date = '2023-10-02'
declare @end as date = '2023-10-15'

; 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_ID = @C_ID
),
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

Drop table #Tbl_Customer
Drop table #tbl_Ledger

Expected Output

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,895 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 102.3K Reputation points
    2023-10-07T12:25:55.3666667+00:00
    declare @C_ID as int = 1001
    declare @start as date = '2023-10-02'
    declare @end as date = '2023-10-15'
    
    
    ; 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)
    ), 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 T_ID, E_Date, Debit, Credit, Balance,
              LAG(Balance) OVER(ORDER BY E_Date) AS prevbal
       FROM   runsum
    )
    SELECT NULL AS ID, NULL AS E_Date, 'Opening' AS Remarks, 
           NULL AS Debit, NULL AS Credit, prevbal AS Balance
    FROM   prevbal
    WHERE  E_Date = @start
    UNION ALL
    SELECT T_ID, E_Date, NULL, Debit, Credit, Balance
    FROM   runsum
    WHERE  E_Date BETWEEN @start AND @end
    ORDER BY ID
    
    

0 additional answers

Sort by: Most helpful