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

Answer 2

You can use lag on date column

select t.*,
    lag(date, 1, date) over (order by date desc) nextdate
from 
(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) t

Answer 3

I believe you want:

select a.*,
       coalesce(lead(date) over (order by date),
                max(date) over ()
               )
from my_table a;

Answer 4

Try this

;with cte as
(
SELECT [DATE] = Cast([date] AS DATE),
       [key],
       [description],
       Lag([date])OVER(ORDER BY  Cast([date] AS DATE) DESC) AS prev_date
FROM   ( VALUES ('20170101','atx','xxx'),
                ('20161228','hfn','xxx'),
                ('20161222','ktn','xxx'),
                ('20161214','yqe','xxx'),
                ('20161204','olp','xxx'),
                ('20161122','bux','xxx')) tc ([date], [key], [description])
)
SELECT [date],
       [Key],
       [Description],
       NextDate = Iif([date] < prev_date, prev_date, [date])
FROM   cte 

Result :

+------------+-----+-------------+------------+
|    date    | Key | Description |  NextDate  |
+------------+-----+-------------+------------+
| 2017-01-01 | atx | xxx         | 2017-01-01 |
| 2016-12-28 | hfn | xxx         | 2017-01-01 |
| 2016-12-22 | ktn | xxx         | 2016-12-28 |
| 2016-12-14 | yqe | xxx         | 2016-12-22 |
| 2016-12-04 | olp | xxx         | 2016-12-14 |
| 2016-11-22 | bux | xxx         | 2016-12-04 |
+------------+-----+-------------+------------+

Answer 5

If your table never has a missing date the following would work.

SELECT CONVERT(DATE,CONVERT(CHAR(10),a.date,120))
,a.key,
,a.description,
,CASE 
    WHEN (SELECT MAX(a.date) FROM my_table a) <> AsAtDateID 
        THEN DATEADD(DAY,1,CONVERT(DATE,CONVERT(CHAR(10),a.date,120)))--This could be a select statement
    ELSE CONVERT(DATE,CONVERT(CHAR(10),a.date,120))
END 
FROM my_table a
ORDER BY Date DESC

Alternatively if there are missing dates then you could use a SQL statement in the CASE to get the next highest date.

SELECT MIN(Date) FROM my_table WHERE Date > a.Date

Not the most performant code, but seeing as we are talking date tables it would work. I'm sure a CTE could be used to do this as well, if you need a bit more performance Using SQL 2008 without LEAD & LAG etc...