Below is data ,i want add remarks and head as per trans_type_ID .
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_C int,Level_Five_ID_D int,Trans_Amount Decimal(10,2),Trans_date date,Trans_Type_ID int,Level_Five_ID_CA int)
INSERT INTO #tbl_Account_L_Five VALUES(124200001,'Abdul Rauf',0,'2023-10-01')
INSERT INTO #tbl_Account_L_Five VALUES(123100001,'Cheque In Hand',0,'2023-10-01')
INSERT INTO #tbl_Account_L_Five VALUES(124200001,'Duties',0,'2023-10-01')
INSERT INTO #tbl_Account_L_Five VALUES(121100004,'MBL 833968',0,'2023-10-01')
insert into #tbl_trans_type VALUES(1,'Bank Payment')
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,121100004,50000,'2023-10-18',1,null)
INSERT INTO #tbl_transection_Five VALUES(2,123100001,Null,1000,'2023-10-18',1,121100004)
INSERT INTO #tbl_transection_Five VALUES(3,124200001,Null,2500,'2023-10-18',1,121100004)
INSERT INTO #tbl_transection_Five VALUES(4,211300001,Null,1500,'2023-10-18',1,121100004)
Declare @Level_Five_ID int=123100001
Declare @startDate date='2023-10-15'
Declare @EndDate date='2023-11-16'
; WITH 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 Trans_ID,Trans_Date ,
Head = coaDCA.Level_Five_Name,
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 =
Case
when
(t.Trans_Type_ID=1 ) then concat('Bank Payment To',' ',isnull(COAC.Level_Five_Name,coaD.Level_Five_Name),',' ,' ',',Amount=',T.Trans_Amount)
End
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_Account_L_Five coaDCA On coaDCA.Level_Five_ID = t.Level_Five_ID_CA
left Join #tbl_trans_type ty On ty.trans_type_ID = t.Trans_Type_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,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 Trans_Date<@startDate
Order by Trans_Date desc,Trans_ID desc
)
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, Trans_Date, Trans_Remarks,HEAD, Debit, Credit, Balance
FROM runsum
WHERE Trans_Date BETWEEN @startDate AND @EndDate
ORDER BY TransDate asc
Drop table #tbl_Account_L_FIve
Drop table #tbl_trans_type
Drop table #tbl_transection_Five
On below parameter
Declare @Level_Five_ID int=121100004
| Level_Five_ID =121100004 | ||||||
|---|---|---|---|---|---|---|
| Trans_ID | TransDate | Trans_Remarks | HEAD | Debit | Credit | Balance |
| NULL | 10/1/2023 | Opening | NULL | 0 | 0 | 0 |
| 1 | 10/18/2023 | Bank Payment To Abdul Rauf/Chequ In Hand/Duties , Amount=50000.00 |
Abdul Rauf Cheque In Hand Duties |
50000 | 0 | 50000 |
On below parameter
Declare @Level_Five_ID int=123100001
| Level_Five_ID =123100001 | ||||||
| Trans_ID | TransDate | Trans_Remarks | HEAD | Debit | Credit | Balance |
| NULL | NULL | Opening | NULL | NULL | NULL | 0 |
| 2 | 10/18/2023 | Bank Payment To Cheque In Hand, ,Amount=1000.00 | MBL 833968 | 0 | 1000 | -1000 |