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
Debit and Credit with Running Balance with Customer Opening Value
Analyst_SQL
3,576
Reputation points
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
Accepted answer
-
Erland Sommarskog 116.5K Reputation points MVP
2023-10-07T12:25:55.3666667+00:00