Need Expected output head wise

Transact-SQL
1

Below is query ,which is giving me output individual head ,but i want balance of head, means all entry in table tbl_ledger must display against head on date filter as in image ,not on @C_ID

If Balance is Positive then it will come in Debit side ,if Balance is Negative then it will come Credit side against head.

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)
Insert into #tbl_Ledger values (14,'2023-10-18',1002,1001,7000)

declare @C_ID as int = 1002
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

2

You can use CROSS APPLY to unpivot your data in #tbl_Ledger. With that it is easier to perform a GROUP BY query and for presentation on Dr / Cr, use CASE expression.

Note : Don't handle the Debit or Credit too early in the query, it complicate things.

Don't really understand your logic and requirement on the variable @start and @end. You can incorporate it yourself on the WHERE clause

select a.Head_ID, c.C_Name, 
       Dr = case when isnull(c.Opening_value, 0) + sum(a.Amount) > 0
                 then isnull(c.Opening_value, 0) + sum(a.Amount)
                 end,
       Cr = case when isnull(c.Opening_value, 0) + sum(a.Amount) < 0
                 then -(isnull(c.Opening_value, 0) + sum(a.Amount))
                 end
from   #tbl_Ledger l
       cross apply
       (
           values
           (Debit_Head_ID,  +T_Amount),
           (Credit_Head_ID, -T_Amount)
       ) a (Head_ID, Amount)
       inner join #Tbl_Customer c on a.Head_ID = c.C_ID
where  l.E_Date >= c.Opening_Date
or     c.Opening_Date is null
group by a.Head_ID, c.C_Name, c.Opening_value
3

@khtan

Above solution is working,thanks,now asking for below query ,that is it fine for individual head ledger

; 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

4

Not sure what you are asking. Please start a new topic as the current question is solved.
Also please do try to understand the earlier query and you may apply it to your other requirement.