Need Expected output head wise

Analyst_SQL 3,526 Reputation points
2023-10-27T07:32:04.06+00:00

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

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.
11,641 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,641 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,328 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,462 questions
0 comments No comments
{count} votes