Below is query
Create table #tbl_Account_L_Five (Level_Five_ID int,Level_Five_Name varchar(50),Opening_Value decimal(10,2),Opening_Date date)
`Create table #tbl_trans_type (Trans_Type_ID int,trans_type_name varchar(50))
Create table #tbl_Transection_Five (Trans_ID int,Level_Five_ID_D int,Level_Five_ID_C int,Level_Five_ID_CA int,Trans_Amount Decimal(10,2),Trans_date date,Trans_Type_ID int)
INSERT INTO #tbl_Account_L_Five VALUES(123100001,'Abdul Rauf',0,'2023-10-01') INSERT INTO #tbl_Account_L_Five VALUES(124200001,'Cheque In Hand',0,'2023-10-01') INSERT INTO #tbl_Account_L_Five VALUES(121100006,'MBL 833968',0,'2023-10-01') INSERT INTO #tbl_Account_L_Five VALUES(124200002,'Duties',0,'2023-10-01')
insert into #tbl_trans_type VALUES(1,'Online') insert into #tbl_trans_type VALUES(2,'Cheque') insert into #tbl_trans_type VALUES(3,'Deposite') insert into #tbl_trans_type VALUES(4,'Tranfer') insert into #tbl_trans_type VALUES(5,'Return')
INSERT INTO #tbl_Transection_Five VALUES(1,null,121100006,NULL,750,'2023-10-04',2) INSERT INTO #tbl_Transection_Five VALUES(1,123100001,null,121100006,250,'2023-10-04',2) INSERT INTO #tbl_Transection_Five VALUES(1,124200001,null,121100006,250,'2023-10-04',2) INSERT INTO #tbl_Transection_Five VALUES(1,124200002,null,121100006,250,'2023-10-04',2)
Declare @Level_Five_ID int=121100006 Declare @StartDate date ='2023-10-01' Declare @EndDate date='2023-11-19'
WITH CTE_H as( select trans_ID,TransDate, Concat( Level_Five_Name, CHAR(13) + CHAR(10)) as Head from #tbl_Account_L_Five c inner join ( select trans_ID,Trans_Date TransDate, Level_Five_ID_CA from #tbl_Transection_Five where Level_Five_ID_D = @Level_Five_ID union select trans_ID,Trans_Date, Level_Five_ID_D from #tbl_Transection_Five where Level_Five_ID_CA = @Level_Five_ID ) t on t.Level_Five_ID_CA = c.Level_Five_ID )
, Unio AS ( SELECT NULL AS Trans_ID, Opening_Date AS Trans_Date, hEAD=nuLL, IIF(Opening_value > 0, Opening_value, 0) AS Debit, IIF(Opening_value < 0, Opening_value, 0) AS Credit, Opening_value AS Amount,'Opening' AS Trans_Remarks FROM #tbl_Account_L_Five WHERE Level_Five_ID = @Level_Five_ID
UNION ALL
SELECT t.Trans_ID,Trans_Date , Head = H.Head,
IIF(Level_Five_ID_D = @Level_Five_ID, Trans_Amount, 0), IIF(Level_Five_ID_C = @Level_Five_ID, Trans_Amount, 0), CASE WHEN Level_Five_ID_D = @Level_Five_ID THEN Trans_Amount WHEN Level_Five_ID_C = @Level_Five_ID THEN -1 * Trans_Amount END,Trans_Remarks =Null
FROM #tbl_transection_five t Left Join #tbl_Account_L_Five coaD On coaD.Level_Five_ID = t.Level_Five_ID_D Left Join #tbl_Account_L_Five coaC On coaC.Level_Five_ID = t.Level_Five_ID_C
left Join #tbl_trans_type ty On ty.trans_type_ID = t.Trans_Type_ID left outer join CTE_H H on H.Trans_ID=t.Trans_ID WHERE Trans_Date > (SELECT Opening_Date FROM tbl_Account_L_Five WHERE Level_Five_ID = @Level_Five_ID) and (Level_Five_ID_D=@Level_Five_ID or Level_Five_ID_C=@Level_Five_ID)),
runsum AS ( SELECT Trans_ID, Trans_Date TransDate ,hEAD, Debit, Credit, SUM(Amount) OVER(ORDER BY Trans_Date ,Trans_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS Balance, Trans_Remarks FROM Unio ),
prevbal AS ( select Top(1)BALANCE prevbal from runsum where TransDate<@startDate Order by TransDate desc,Trans_ID desc)
,CTE_F as( SELECT NULL AS Trans_ID, NULL AS TransDate, 'Opening' Trans_Remarks, null aS HEAD,NULL AS Debit, NULL AS Credit, prevbal AS Balance FROM prevbal UNION all SELECT Trans_ID, TransDate, Trans_Remarks,HEAD, Debit, Credit, Balance FROM runsum WHERE TransDate BETWEEN @startDate AND @EndDate )
select Trans_ID, TransDate, Trans_Remarks,HEAD, Debit, Credit, Balance from CTE_F Drop table #tbl_Account_L_Five Drop table #tbl_trans_type Drop table #tbl_Transection_Five
I want below output