Have a column with the lowest possible next value (self-joining a table)

Question

I am looking for a way to get the lowest next value in a sequence. Basically, I have a dataset of Dates and I want it to return the next day unless it's the latest date in the database, then I want it to return this instead.

My current query looks like this and almost works - of course up to the point where I want the latest possible value instead of the next one:

SELECT
  a.date,
  a.key,
  a.description,
  b.date NextDate
FROM
  my_table a
  CROSS APPLY (SELECT TOP 1
  b.date
FROM
  my_table b
WHERE
  a.key = b.key AND
  a.date < b.date) b

Sample data:

+----------+-----+-------------+
|   date   | key | description |
+----------+-----+-------------+
| 20170101 | atx | xxx         |
| 20161228 | hfn | xxx         |
| 20161222 | ktn | xxx         |
| 20161214 | yqe | xxx         |
| 20161204 | olp | xxx         |
| 20161122 | bux | xxx         |
+----------+-----+-------------+

What the result should look like:

+----------+-----+-------------+----------+
|   date   | key | description | NextDate |
+----------+-----+-------------+----------+
| 20170101 | atx | xxx         | 20170101 |
| 20161228 | hfn | xxx         | 20170101 |
| 20161222 | ktn | xxx         | 20161228 |
| 20161214 | yqe | xxx         | 20161222 |
| 20161204 | olp | xxx         | 20161214 |
| 20161122 | bux | xxx         | 20161204 |
+----------+-----+-------------+----------+

Answer 1

You can use a case expression to do this.

SELECT
  a.date,
  a.key,
  a.description,
  case when date = max(a.date) over() then date
  else (select min(date) from mytable b where a.date < b.date)  end as NextDate
FROM
  my_table a