Click here to Skip to main content
16,015,973 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more: , +
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) 
SQL



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
Posted
Updated 15-Nov-23 2:53am
v2
Comments
Richard Deeming 15-Nov-23 8:31am    
And?

You seem to have forgotten to ask a question.

I've just run your two queries locally, and after adding the missing # in the subquery for tbl_Account_L_Five, it returns output identical to your "expected output" screenshot.

So you need to update your question to explain precisely what you are trying to do, what the problem is, and where you are stuck.
[no name] 15-Nov-23 11:46am    
I don't see anything that resembles an "account number". Or "where" the amount is. Using the same "model" as any decent "ledger system" is the easiest way to avoid brain fog. I'll even use separate fields for "Debit" and "Credit"; even if it is "redundant".
akhter86 15-Nov-23 12:10pm    
@Gerry Schmitz i already used debit and credit field ,i did not understand you,( easiest way to avoid brain fog)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900