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

Time:07-05

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

CodePudding user response:

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
  • Related