SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,641 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Below is query ,which is giving me output individual head ,but i want trail balance 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 Dr 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