Account ledger query with debit and credit

Question

Hi All respected community member, i am trying to develop ledger query from long time still i could not get ,please do review and help me out ,

if you have any suggestion for restructure #tbl_Transection_test table ,please let me know or guide me thaks..

I have table and rows value

 Create  table #tbl_Transection_Test (Trans_ID varchar(50), Level_Five_ID int, Trans_Amount_D int, Trans_Amount_C  int,  Trans_Date date)

Insert into #tbl_Transection_Test values ('CP-00001' ,123100001, 2000,Null, '2024-11-22')

Insert into #tbl_Transection_Test values ('CP-00001' ,122100001, Null,5000, '2024-11-22')

Insert into #tbl_Transection_Test values ('CP-00001' ,124200001, 2000,Null, '2024-11-22')

Insert into #tbl_Transection_Test values ('CP-00001' ,124200002, 1000,Null, '2024-11-22')

i want below result When i pass Level_Five_ID parameter 122100001

User's image

i want below result When i pass Level_Five_ID parameter 123100001

User's image

Accepted Answer

Hi @Analyst_SQL

Not sure I understand right, but try this:

DECLARE @Level_Five_ID INT 
SET @Level_Five_ID = 123100001

SELECT Trans_ID
      ,CASE WHEN Trans_Amount_D IS NULL 
	        THEN (SELECT STRING_AGG(Level_Five_ID,' ') FROM #tbl_Transection_Test B WHERE A.Trans_ID =B.Trans_ID AND B.Trans_Amount_D IS NOT NULL)
			WHEN Trans_Amount_C IS NULL 
			THEN (SELECT STRING_AGG(Level_Five_ID,' ') FROM #tbl_Transection_Test B WHERE A.Trans_ID =B.Trans_ID AND B.Trans_Amount_C IS NOT NULL)
       END AS Level_Five_ID
	  ,Trans_Date
      ,ISNULL(Trans_Amount_D,0) AS Trans_Amount_D 
      ,ISNULL(Trans_Amount_C,0) AS Trans_Amount_C
	  ,ISNULL(Trans_Amount_D,0) - ISNULL(Trans_Amount_C,0) AS Balance	  
FROM #tbl_Transection_Test A
WHERE Level_Five_ID = @Level_Five_ID

Note that STRING_AGG works in SQL Server 2017 (14.x) and later. For versions before 2017, an alternative method is using Stuff function along with for xml path.

Best regards,

Cosmog

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer

With the information you have given, it would be:

SELECT Trans_ID, Level_Five_ID, Trans_Date, 
       isnull(Trans_Amount_D, 0) AS Trans_Amounnt_D, isnull(Trans_Amount_C, 0) AS Trans_Amounnt_C, 
       isnull(Trans_Amount_D, -Trans_Amount_C) AS Balance
FROM   #tbl_Transection_Test
WHERE  Level_Five_ID = 123100001

I suspect that there is more hiding there, but if so, you need to clarify. I am not going to try to read between the lines.

Share via

Account ledger query with debit and credit

1 additional answer

Your answer