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

i want below result When i pass Level_Five_ID parameter 123100001

Answer 1

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 2

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.