I have a table with ID, Date, Value. For each row, I want to return the most recent non-null value where Date <= Date minus 1 year.

I'm trying variations of window functions with last_value or lag, and partitioning on ID, order by Date, with the IGNORE NULLS clause. However, I'm not sure where to include the condition where Date <= Date minus 1 year

Sample Data:

Desired Results:

CodePudding user response：

Consider group by and two joins instead of window functions, e.g.

select Id, Date, Value
from DataTable d1
inner join (
    select Id, Date as Most_Recent_Prior_Year_Date, max(Value) as Most_Recent_Prior_Year_Value 
    -- max(Value) because there could be many values for that day
    from DataTable d3
    inner join (
        select Id, max(Date) as MostRecentDate
        from DataTable
        where Date <= DATEADD(year, -1, CURRENT_DATE())
        group by Id
    ) d4 on d3.Id = d4.Id AND d3.Date = d4.MostRecentDate
    group by Id, Date
) d2 on d1.Id = d2.Id

CodePudding user response：

IF you strickly have only one row per day per id then you can use:

A single LEFT JOIN and WHERE/QUALIFY will work here:

SELECT 
    a.ID,
    a.Date,
    a.Value
    b.Data AS Most_Recent_Prior_Year_Date
    b.Value AS Most_Recent_Prior_Year_Value
FROM table AS a
LEFT JOIN table AS b 
    on a.id = b.id AND DATEADD('year',-1,a.date) >= b.date
QUALIFY ROW_NUMBER() OVER( PARTITION BY a.id, a.date ORDER BY b.date DESC NULLS LAST) = 1

Otherwise you will want to first separate out a single value per day aka MAX, MIN, FIRST_VALE and then run the above over that output.