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