Converting Old Running Total Code to Window Functions

  • Comments posted to this topic are about the item Converting Old Running Total Code to Window Functions

  • 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

    • This reply was modified 2 weeks, 1 day ago by  Thomas Franz.

    God is real, unless declared integer.

  • 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