CODEWITHSUNDEEP
mysqlsql
Tom Krakov
Tom Krakov

Reputation: 81

Table with date, then next date

I am looking to generate a table with one date followed by the next date in the set, given some criteria.

Raw Data:

id       date
1     2000-01-01
1     2000-01-02
1     2000-01-04
1     2000-01-10
1     2000-01-14
1     2000-01-15
1     2000-01-16
1     2000-01-18
2     2000-02-01
2     2000-02-02
2     2000-02-04
2     2000-02-10
2     2000-02-14
2     2000-02-15
2     2000-02-16
2     2000-02-18

Would result in:

   id       date           date
    1     2000-01-01    2000-01-02
    1     2000-01-02    2000-01-04
    1     2000-01-04    2000-01-10
    1     2000-01-10    2000-01-14
    1     2000-01-14    2000-01-15
    1     2000-01-15    2000-01-16
    1     2000-01-16    2000-01-18
    1     2000-01-18    NULL
    2     2000-02-01    2000-02-02
    2     2000-02-02    2000-02-04
    2     2000-02-04    2000-02-10
    2     2000-02-10    2000-02-14
    2     2000-02-14    2000-02-15
    2     2000-02-15    2000-02-16
    2     2000-02-16    2000-02-18
    2     2000-02-18    NULL

I know I have to use a self join, but I don't know how to return the sequentially next largest value, given that specific id. Thanks.

Upvotes: 3

Views: 53

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

In most databases, you could just use the lead() function. In MySQL, the easiest way is probably a correlated subquery:

select t.*,
       (select t2.date
        from rawdata t2
        where t2.id = t.id and t2.date > t.date
        order by t2.date
        limit 1
       ) as next_date
from rawdata t;

Upvotes: 1

Guillermo
Guillermo

Reputation: 1

You could use [date + interval 1 day] if it is a mysql or postgresql DB

For example:

select t.id, t.date, t.date + interval 1 days as date 2 from rawdata t;

For oracle you could do something like this:

select t.id , t.date, TO_DATE(t.date, 'YYYY-MM-DD') + 1 from rawdata t;

Upvotes: 0

Related Questions