SQL Server: variable window function

Question

Using SQL Server 2016, I have two tabels like these:

T1
-----------------------
User |  Y   | M | V
---- ------------------
US1  | 2011 | 1 | 87.5
US1  | 2011 | 2 | 76.4
...  | ...  | ..| ...
US1  | 2018 | 7 | 4.7
US1  | 2018 | 8 | 7.7
US1  | 2018 | 9 | 16.3
US1  | 2018 | 10| 11.1
US1  | 2018 | 11| 98.8
US1  | 2018 | 12| 65.7
US2  | 2011 | 1 | 44.5
US2  | 2011 | 2 | 34.7
...  |  ... |...| ... 
US2  | 2018 | 6 | 18.7
US2  | 2018 | 7 | 5.1
US2  | 2018 | 8 | 1.0 
US2  | 2018 | 9 | 4.4
US2  | 2018 | 10| 22.6
US2  | 2018 | 11| 66.5
US2  | 2018 | 12| 23.7

T2
----------
User | S | 
---- -----
US1  | 3 | 
US2  | 5 |

I would like to get a running SUM like this

SELECT t1.Y
   ,t1.M
   ,t1.User
   ,SUM(t1.V) OVER (PARTITION BY t1.User ORDER BY t1.Y,t1.M ROWS BETWEEN t2.S PRECEDING AND CURRENT ROW)
FROM t1
JOIN t2 ON t1.User = t2.User

So my expected Output for the last 2 Months is

US1  | 2018 | 11| 133,9 (98.8+11.1+16.3+7.7)
US1  | 2018 | 12| 191.9 (65.7+98.8+11.1+16.3)
US2  | 2018 | 11| 118.3 (66.5+22.6+4.4+1.0+5.1+18.7)
US2  | 2018 | 12| 123.3 (23.7+66.5+22.6+4.4+1.0+5.1)

but I can't use t2.S as variable in the Window Function.

Is there a way to do that using Window Funcitons?

Answer 1

I don't think expressions are allowed for window frame specifications. You can do this using apply:

SELECT t1.Y, t1.M, t1.User, t2.sum_v
FROM t1 CROSS APPLY
     (SELECT SUM(t.v) as sum_v
      FROM (SELECT tt1.v, tt2.S
                   ROW_NUMBER() OVER (ORDER BY tt1.Y DESC, tt1.M DESC) as seqnum
            FROM t1 tt1 JOIN
                 t2 tt2
                 ON tt1.User = tt2.User
            WHERE tt1.User = t1.User
           ) t
      WHERE seqnum <= S
     ) t2

EDIT:

The above doesn't work because the ordering is in the wrong directly. In any case, it is equivalent to a self join, and that is quite expensive.

Can this just be done with window functions?

I think the answer is "yes":

with t1 as (
      select t1.*, t2.s,
             sum(v) over (partition by user order by y, m) as running_v
      from t1 join
           t2
           on t1.user = t2.user
     ) t
select t.*,
       (running_v - running_v_s)
from (select t.*,
             lag(running_v, s) over (partition by user order by y, m) as running_v_s
      from t
     ) t;

Although you cannot use expressions for window frames, you can use them for lag(). I think this approach is likely to be the best performing method.