Ledger query need to modify

See more:

SQL

SQL-Server

ASP.NET5

, +

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