# Use expression in window function that references columns from the current row

Suppose I have the following query that uses window function:

``````SELECT id
, var
, num
, SUM(var * num) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS calc
FROM (VALUES
(1, 0.1, 7),
(2, 0.7, 1),
(3, 0.3, 9),
(4, 0.9, 5),
(5, 0.5, 3)
) AS t(id, var, num)
``````

And the following result:

``````id | var | num | calc | explanation
1  | 0.1 | 7   | 0.7  | 0.1*7
2  | 0.7 | 1   | 1.4  | 0.1*7 + 0.7*1
3  | 0.3 | 9   | 4.1  | 0.1*7 + 0.7*1 + 0.3*9
4  | 0.9 | 5   | 7.9  | 0.7*1 + 0.3*9 + 0.9*5
5  | 0.5 | 3   | 8.7  | 0.3*9 + 0.9*5 + 0.5*3
``````

Is is possible to reference the `var` column from the outside inside the `SUM() OVER ()`? For example:

``````id | var | num | calc | sum of f(r.var, w.var, w.num)
1  | 0.1 | 7   | ...  | iif(0.1<=0.1,0.1,0.1)*7
2  | 0.7 | 1   | ...  | iif(0.7<=0.1,0.7,0.1)*7 + iif(0.7<=0.7,0.7,0.7)*1
3  | 0.3 | 9   | ...  | iif(0.3<=0.1,0.3,0.1)*7 + iif(0.3<=0.7,0.3,0.7)*1 + iif(0.3<=0.3,0.3,0.3)*9
4  | 0.9 | 5   | ...  | iif(0.9<=0.7,0.9,0.7)*1 + iif(0.9<=0.3,0.9,0.3)*9 + iif(0.9<=0.9,0.9,0.9)*5
5  | 0.5 | 3   | ...  | iif(0.5<=0.3,0.5,0.3)*9 + iif(0.5<=0.9,0.5,0.9)*5 + iif(0.5<=0.5,0.5,0.5)*3
``````

Basically I want to calculate running sum of an expression that references `var` column in the current row and `var` column in the current window. `iif` and multiplication is just an example.

The solution should work in SQL Server but generic answer would be best.

Here is a db<>fiddle where I was able to achieve the result with correlated queries but I want to use window functions.

• It could be arbitrary number of rows e.g. 100 or there could be no `ROWS BETWEEN` at all. The values do not matter, one can post the output that proves that `SUM` was calculated for each row in window based on the rows inside and outside the window. – Salman A Nov 20 '19 at 10:15

Is this what you are looking for ?

``````create table #t(id int, vars decimal(3,1), num int)
insert into #t VALUES
(1, 0.1, 7),
(2, 0.7, 1),
(3, 0.3, 9),
(4, 0.9, 5),
(5, 0.5, 3)

;With CTE as
(
select
t.id
,t.vars
,t.num
,isnull(lag(vars ,1) OVER (ORDER BY id ),0) AS vars1
,isnull(lag(vars ,2) OVER (ORDER BY id ),0) AS vars2
,isnull(lag(num ,1) OVER (ORDER BY id ),0) AS num1
,isnull(lag(num ,2) OVER (ORDER BY id ),0) AS num2
FROM  #t t
)

select *
,iif(vars<=0.1,vars,vars2)*num2 +
iif(vars<=vars1,vars,vars1)*num1 +
iif(vars<=vars,vars,vars)*num
from CTE

drop table #t
``````
• I cannot hard code 1 and 2... the size of window could be anything... even unbounded. – Salman A Nov 20 '19 at 13:02
• @SalmanA, do you mean to say my output is correct ?You should explain the biz. reuirement for size of window or unbounded.How do I understand size of window ? – KumarHarsh Nov 20 '19 at 16:55