How to create a column with the next value in the list

Question

I have a SQL table as such

id	date	value
1	01/01/2019	50
1	04/01/2019	25
2	01/01/2019	63
2	15/01/2019	43

I want to create another column called next_date, and next_value that computes the next date and value grouped by id in the list, so that the new table will look:

id	date	value	next_date	next_value
1	01/01/2019	50	04/01/2019	25
1	04/01/2019	25	None	None
2	01/01/2019	63	15/01/2019	43
2	15/01/2019	43	None	None

score 1 · Answer 1 · answered Jul 04 '22 at 10:37

1

You may use the LEAD() analytic function here:

SELECT id, date, value,
       COALESCE(CONVERT(varchar, LEAD(date) OVER (PARTITION BY id ORDER BY date), 103), 'None') next_date,
       COALESCE(CAST(LEAD(value) OVER (PARTITION BY id ORDER BY date) AS varchar(max)), 'None') AS next_value
FROM yourTable
ORDER BY id, date;

Demo

answered Jul 04 '22 at 10:37

Tim Biegeleisen

502,043
27
286
360

Thank you Tim. If I want the max value over the next 7 days, how can I do so? – user19478610 Jul 04 '22 at 10:49
Please ask a new question, this one has already been answered. – Tim Biegeleisen Jul 04 '22 at 11:11

How to create a column with the next value in the list

1 Answers1

Demo