How to max value over the next 7 days in my list

July 4, 2022

I have a SQL table:

id,date,value
1,01/01/2019,50
1,13/01/2019,24
1,19/01/2019,53
2,05/01/2019,50
2,11/01/2019,24
2,24/01/2019,53

I want to create a new column that computes that max value over the next 14 days grouped by id. If the difference between the date in the current row and the next is greater than 14, return None or Null.

The new table will be:

id,date,value,max_14
1,01/01/2019,50,50
1,13/01/2019,24,53
1,19/01/2019,53, None
2,05/01/2019,50,50
2,11/01/2019,24,53,
2,24/01/2019,53,None

>Solution :

You can use a sub-query for this:

select t.*, (
    select max(value)
    from t as x
    where x.id = t.id
    and   x.date >= t.date
    and   x.date < dateadd(day, 14, t.date)
)
from t