[SOLVED] Reference outer context in LAG function

Reference outer context in LAG function

I would like to make next query:

select ...,
(
    source.F1 - /* current row */
    LAG(IIF(source /*current row*/.F2 = source /*lag row*/.F2, source.F1, 0), 12, source.F1)
    OVER (...)    
) as alias
from source

Desired result: when row selected by LAG function (number 12 in partition) match condition currentRow.F2 = rowSelectedByLag.F2 then return rowSelectedByLag.F1, otherwise 0.

Issue: how to reference current row in IIF condition?

Solution

Use a derived table or CTE to return the value of the LAGged column, and then use a CASE expression. In pseudo SQL:

WITH CTE AS (
    SELECT {OtherColumns},
           LAG(NeededColumn) OVER (ORDER BY {Expression(s)}) AS LaggedColumn1,
           LAG(OtherColumn) OVER (ORDER BY {Expression(s)}) AS LaggedColumn2
    FROM dbo.YourTable)
SELECT {OtherColumns},
       CASE LaggedColumn2 WHEN OtherColumn THEN LaggedColumn1 ELSE 0 END AS ColumnAlias
FROM CTE;

If you didn't want to use a CTE/derived table you would need to use the 2 LAGs in the CASE expression:

SELECT {OtherColumns},
       CASE LAG(OtherColumn) OVER (ORDER BY {Expression(s)}) WHEN OtherColumn THEN LAG(NeededColumn) OVER (ORDER BY {Expression(s)}) ELSE 0 END AS ColumnAlias
FROM dbo.YourTable