0

I have a large table with a DATETIME2 column. I would like to write a query that relates each distinct date in that column to the date from the same column that immediately follows the first date. I suspect that I need to use a self-join, but I'm unsure about what to do next. How do I write such a query?

I don't have any preferences about how the case of the last date is handled. Its NextDate can be set to NULL or the row can be excluded.

Example:

DROP TABLE IF EXISTS #Dates

CREATE TABLE #Dates (
    MyDate DATETIME2 NOT NULL
)

INSERT INTO #Dates (MyDate) VALUES ('20210101')
INSERT INTO #Dates (MyDate) VALUES ('20210102')
INSERT INTO #Dates (MyDate) VALUES ('20210103')
INSERT INTO #Dates (MyDate) VALUES ('20210104')
INSERT INTO #Dates (MyDate) VALUES ('20210105')

SELECT 
    MyDate AS CurrentDate
    -- , ??? AS NextDate
FROM #Dates
ORDER BY CurrentDate

How do I get:

+-------------+-------------+
| CurrentDate |  NextDate   |
+-------------+-------------+
|    20210101 |    20210102 |
|    20210102 |    20210103 |
|    20210103 |    20210104 |
|    20210104 |    20210105 |
|    20210105 |    NULL     |
+-------------+-------------+
1
  • Conveniently your sample dates are sequential and gapless. Is that an assumption or reality? Think carefully, know / analyze your data, understand your actual goal.
    – SMor
    Mar 18 at 12:14
3

Unless you're on a very old version of SQL Server, the LEAD function should work.

SELECT 
    MyDate AS CurrentDate,
    LEAD(MyDate, 1, NULL) OVER
    (
        ORDER BY
            MyDate
    ) AS NextDate
FROM #Dates
ORDER BY CurrentDate;
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.