SQL Server Lag function adding range

Question

Hi I am a newbie when it comes to SQL and was hoping someone can help me in this matter. I've been using the lag function here and there but was wondering if there is a way to rewrite it to make it into a sum range. So instead of prior one month, i want to take the prior 12 months and sum them together for each period. I don't want to write 12 lines of lag but was wondering if there is a way to get it with less lines of code. Note there will be nulls and if one of the 12 records is null then it should be null.

I know you can write write subquery to do this, but was wondering if this is possible. Any help would be much appreciated.

Answer 1

You want the "window frame" part of the window function. A moving 12-month average would look like:

select t.*,
       sum(balance) over (order by period rows between 11 preceding and current row) as moving_sum_12
from t;

You can review window frames in the documentation.

If you want a cumulative sum, you can leave out the window frame entirely.

I should note that you can also do this using lag(), but it is much more complicated:

select t.*,
       (balance +
        lag(balance, 1, 0) over (order by period) +
        lag(balance, 2, 0) over (order by period) +
        . . .
        lag(balance, 11, 0) over (order by period) +
       ) as sum_1112
from t;

This uses the little known third argument to lag(), which is the default value to use if the record is not available. It replaces a coalesce().

EDIT:

If you want NULL if 12 values are not available, then use case and count() as well:

select t.*,
       (case when count(*) over (order by period rows between 11 preceding and current row) = 12
             then sum(balance) over (order by period rows between 11 preceding and current row)
        end) as moving_sum_12
from t;