Need help with a potential loop (or how would you do this)?

Copper Contributor

Hi all,

I'm pretty sure this can be done with multiple LEAD()/CASE statements but I'm trying to find a way to almost automate this for simplicity sake and to help me learn something new.

Attached is some example data that is in table #Test.

clipboard_image_0.png

On the left I have Keys which are individual users. I think what I need to do is a loop for each unique Key; 93246499, 93246616, 93246697 and 93246831.

Each has a transcode associated with them:

Code 36 = Charge

Code18 = Paid

Code 15 & 31 = Waive

What I am wanting is for each instance of the 36 Code (Charge) to look at the next row (I'm currently using lead() for the Date Paid) and determine if the transcode is different. Here are the different scenarios:

In all of the examples below this is by Key if the Key is <> Row 1 Key then it stops at that step.

Row 1 Code 36 Row 2 Code 36 = Stop and count as UnPaid (this is already setup)

Row 1 Code 36 Row 2 Code 15, 31 = Set Waive Amount to the Late Amount (this is already setup).

Row 1 Code 36 Row 2 Code 18, 19 = Continue searching next row until reaching Code 15, 31 or 36 and SUM the Late columns. IF we reach code 15 or 31 then set Waive amount to the Late amount.

Once these are done I plan on doing SELECT * FROM #TEST WHERE Trans_Code = 36 to get the following output...

clipboard_image_1.png

 

I'm working on finding an instance where the Trans_Code = 18/19 for ROWS 2-3+ but again it would be the same as above but the PAID amount would instead be a SUM of all those Rows. Will have an example shortly...

Here's a better example of what I'm looking for.

clipboard_image_2.png

End Result should be (note I'm unsure of what to do with the Date yet so you can ignore the format of that):

Capture4.PNG

1 Reply

Here's something I came up with so far... but I still think there should be a more simple and effective means of doing this without the bulk of code.

 

 

SELECT *
	  ,CASE WHEN LEAD([Key], 1) OVER(PARTITION BY [KEY] ORDER BY [Date]) = [Key] THEN 
		    CASE WHEN LEAD(TRANS_CODE, 1) OVER(PARTITION BY [KEY] ORDER BY [Date]) IN ('18', '19') THEN [Amount Paid] + LEAD(Charge, 1) OVER(PARTITION BY [KEY] ORDER BY [Date]) + ISNULL(
				 CASE WHEN LEAD([Key], 2) OVER(PARTITION BY [KEY] ORDER BY [Date]) = [Key] THEN
					  CASE WHEN LEAD(TRANS_CODE, 2) OVER(PARTITION BY [KEY] ORDER BY [Date]) IN ('18', '19') THEN LEAD(Charge, 2) OVER(PARTITION BY [KEY] ORDER BY [Date]) + ISNULL(
						   CASE WHEN LEAD([Key], 3) OVER(PARTITION BY [KEY] ORDER BY [Date]) = [Key] THEN
								CASE WHEN LEAD(TRANS_CODE, 3) OVER(PARTITION BY [KEY] ORDER BY [Date]) IN ('18', '19') THEN LEAD(Charge, 3) OVER(PARTITION BY [KEY] ORDER BY [Date]) + ISNULL(
									 CASE WHEN LEAD([Key], 4) OVER(PARTITION BY [KEY] ORDER BY [Date]) = [Key] THEN
										  CASE WHEN LEAD(TRANS_CODE, 4) OVER(PARTITION BY [KEY] ORDER BY [Date]) IN ('18', '19') THEN LEAD(Charge, 4) OVER(PARTITION BY [KEY] ORDER BY [Date])
											   ELSE 0
										   END
									  END, 0)
									 ELSE 0
								 END
						    END, 0)
						   ELSE 0
					   END
				  END, 0)
				 ELSE 0 END
		    ELSE 0
		END
  FROM #Test
 WHERE [Key] = '93246916'