Share via

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.
13,361 questions
0 comments
Accepted answer
  1. Erland Sommarskog 107.2K 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
Most helpful Newest Oldest