Need Help to modify sql query

Akhter's profile image Akhter posted 1 year ago in Running SQL scripts Permalink

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

Description

Please login to leave a reply, or register at first.