CODEWITHSUNDEEP
sqlsql-server
user19478610
user19478610

Reputation: 11

Find max value over the next 7 days for each group

I have a SQL table:

id date value
1 01/01/2019 50
1 01/13/2019 24
1 01/19/2019 53
2 01/05/2019 50
2 01/11/2019 24
2 01/24/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 01/13/2019 24 53
1 01/19/2019 53 None
2 01/05/2019 50 50
2 01/11/2019 24 53
2 01/24/2019 53 None

Upvotes: 0

Views: 69

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272216

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

Upvotes: 1

Related Questions