sqlsql-serveranalytic-functions

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