Post reply

Converting Old Running Total Code to Window Functions

  • September 30, 2024 at 12:00 am

    #4453769

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

  • September 30, 2024 at 4:11 pm

    #4463856

    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 months, 3 weeks ago by  Thomas Franz.

    God is real, unless declared integer.

  • September 30, 2024 at 4:22 pm

    #4463858

    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