SQL window function with date filter

Looking to do a LAG/LAST_VALUE/MAGIC window function on a table which somehow needs to include a date filter (as in how far back it should go). I’m trying to get the value as of 7 days ago onto every row. Not sure this can be achieved with a fancy window function in a sparse data set. As the data is quite large, it would need to be an efficient query query as well.

Test Query (wrong result):

WITH TestData AS (
    SELECT 'MyKey' AS KeyCol, CAST('2019-05-06' AS DATE) as DateCol, 20 as ValueCol
    UNION ALL
    SELECT 'MyKey' AS KeyCol, CAST('2019-05-12' AS DATE) as DateCol, 12 as ValueCol
    UNION ALL
    SELECT 'MyKey' AS KeyCol, CAST('2019-06-01' AS DATE) as DateCol, 41 as ValueCol
    UNION ALL
    SELECT 'MyKey' AS KeyCol, CAST('2019-06-14' AS DATE) as DateCol, 21 as ValueCol
    UNION ALL
    SELECT 'MyKey' AS KeyCol, CAST('2019-06-15' AS DATE) as DateCol, 2 as ValueCol
    UNION ALL
    SELECT 'MyKey' AS KeyCol, CAST('2019-06-20' AS DATE) as DateCol, 32 as ValueCol
)

SELECT KeyCol, DateCol, ValueCol
, LAG(ValueCol) OVER (ORDER BY DateCol ASC) ThisShouldShowValueAsOf7DaysAgo

from TestData

Output:

+--------+------------+----------+---------------------------------+
| KeyCol |  DateCol   | ValueCol | ThisShouldShowValueAsOf7DaysAgo |
+--------+------------+----------+---------------------------------+
| MyKey  | 2019-05-06 |       20 | NULL                            |
| MyKey  | 2019-05-12 |       12 | 20    -> should be NULL         |
| MyKey  | 2019-06-01 |       41 | 12    -> correct                |
| MyKey  | 2019-06-14 |       21 | 41    -> correct                |
| MyKey  | 2019-06-15 |        2 | 21    -> should be 41           |
| MyKey  | 2019-06-20 |       32 | 2     -> should be 41           |
+--------+------------+----------+---------------------------------+

Answer

I think you want apply:

select t.*, t2.valuecol
from t outer apply
     (select top (1) t2.*
      from t t2
      where t2.keycol = t.keycol and
            t2.date < dateadd(day, -7, t.date)
      order by t2.date desc
     ) t2

In other databases, you could use a fancy window frame with range between. However, SQL Server does not support such functionality.

Advertisement

Answer