Converting Old Running Total Code to Window Functions

Question

Converting Old Running Total Code to Window Functions

Steve Jones - SSC Editor

SSC Guru

Points: 738043
More actions
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

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Thomas Franz Hall of Fame Points: 3927 More actions · Answer 1

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 8 months ago by Thomas Franz.

God is real, unless declared integer.

Steve Jones - SSC Editor SSC Guru Points: 738043 More actions · Answer 2

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.

Converting Old Running Total Code to Window Functions

Cookies on SQLServerCentral