Nov 08 2019 07:23 AM
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.
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...
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.
End Result should be (note I'm unsure of what to do with the Date yet so you can ignore the format of that):
Nov 08 2019 07:54 AM - edited Nov 08 2019 07:59 AM
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'