September 30, 2024 at 12:00 am
Comments posted to this topic are about the item Converting Old Running Total Code to Window Functions
September 30, 2024 at 4:11 pm
Nice solution.
Instead of combining LAG() and SUM() OVER you could have used just LAG - at least when it are just a fixed number of the last 4 entries. For much more entries it may become annoying to repeat the LAG multiple times. I also included an ISNULL() in the subquery, otherwise the running_total would become NULL, when one (or more) of the 5 summands are NULL, since a simple + doesn't ignore NULL values as SUM() does. When [balance] is defined as NOT NULL this is not necessary.
SELECT *
, t.BalanceNN
+ LAG(t.BalanceNN, 1, 0) over my_win
+ LAG(t.BalanceNN, 2, 0) over my_win
+ LAG(t.BalanceNN, 3, 0) over my_win
+ LAG(t.BalanceNN, 4, 0) over my_win AS running_total
FROM (SELECT *, ISNULL(Balance, 0) as BalanceNN FROM dbo.accounts) as t
WINDOW my_win AS (ORDER BY TransactionDate) -- works starting with SQL 2022 and prevents the need to repeat the OVER condition multiple times
God is real, unless declared integer.
September 30, 2024 at 4:22 pm
Thanks, interesting approach. I didn't think about adding the LAGs, which makes sense.
I certainly didn't think about NULLs in the balance. It wasn't part of the original spec, but you're right, null data in there would cause an issue if allowed.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply