I have below table and Data.
i am trying to display ledger of a customer,with their debit and credit value,with running balance,
query is below ,is giving me correct output except Head Column ,
Problem is that ,when
Level_Five_ID_CA
is null in
#tbl_Transection_Five
,then Debit side heads must display in one row against trans_ID and their Credit value should not be repeated,as below query is repeating credit value with each row.
I am stuck on repeating credit value with each row ,which should not be.
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)
I want below output
Imgur: The magic of the Internet[
^][
^]
What I have tried:
I tried below
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 = --(CONCAT( T.Cheque_No ,' ', T.Cheque_Bank ,' ' ,'Cheque',' Date', ' ' ,T.Cheque_Date, ' ' ,'Branch',' ' ,T.Cheque_Branch , ' ','Rs.',CONVERT(varchar(50), CAST(T.Trans_Amount AS money),1),' ','Received From ' ,COAc.Level_Four_Name )) as Trans_Remarks
Case when (t.trans_type_ID=2 ) then concat('Rs.',CONVERT(Varchar(50), Cast(T.trans_Amount as money),1), coac.Level_Five_Name)
when
(t.Trans_Type_ID=7 and coac.Level_Five_ID=410101) then concat('Cash Received From',' ','Walking',',',' Invoice No' ,' ',',Amount=',T.Trans_Amount)
when
(t.Trans_Type_ID=7 and coac.Level_Five_ID!=410101) then concat('Cash Received From',' ',coac.Level_Five_Name,',',' Invoice No' ,' ',',Amount=',T.Trans_Amount)
when
(t.Trans_Type_ID=8 ) then concat('Cash Payment To',' ',coaD.Level_Five_Name,',' ,' ',',Amount=',T.Trans_Amount)
when
(t.Trans_Type_ID=1 ) then concat('Bank Payment To',' ',coaD.Level_Five_Name,',' ,' ',',Amount=',T.Trans_Amount)
When
(t.Trans_Type_ID=2) then concat(COAD.Level_Five_Name ,'
From',' ',Coac.Level_Five_Name, '',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_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