Forum Discussion
KruegerXIII
Nov 08, 2019Copper Contributor
Need help with a potential loop (or how would you do this)?
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 i...
KruegerXIII
Nov 08, 2019Copper Contributor
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'