Hello, I need some help please)))
there's a table
| ID | A_ID | Value | Date |
|---|---|---|---|
| 1 | 1 | 67 | 2019-09-01 |
| 2 | 135 | 7 | 2018-10-27 |
| 3 | 2 | 5 | 2020-02-10 |
| 4 | 3 | 0 | 2018-12-30 |
| 5 | 5 | 81 | 2018-08-03 |
| 6 | 135 | 5 | 2020-02-11 |
| 7 | 135 | 68 | 2018-12-27 |
| 8 | 9 | 15 | 2018-10-12 |
| 9 | 5 | 77 | 2020-03-10 |
| 10 | 8 | 6 | 2019-06-23 |
I need to select for each A_ID the next greater date minus 1 second.
If there's no such date for a particulat A_ID, so we need to write '2099-01-01'
I think you want LEAD - something like: LEAD([Date], 1, '2099-01-01') OVER(PARTITION BY A_ID ORDER BY [Date])
As for the 1 second - a date doesn't have any time associated with it so I am not sure what that would accomplish. If you are trying to define the end date then just use the next start date and make sure you check for less than instead of BETWEEN.
This whole train of thought is a REALLY bad idea because you're missing 1 whole second from every day.