SQL Query Creating Start and End Dates

Question

Alright, let's say I have a table that looks like this:

 ID   | DATE
 2  | 2010-08-12
 2  | 2010-08-16 
 2  | 2010-08-17 
 2  | 2010-12-21 
 2  | 2010-12-22 
 2  | 2011-05-25

anyone have an idea on how to query it so the data looks like

 ID   | STARTDATE  | ENDDATE
 2  | 2010-08-12 | 2010-08-15
 2  | 2010-08-16 | 2010-08-16
 2  | 2010-08-17 | 2010-12-20
 2  | 2010-12-21 | 2010-12-21
 2  | 2010-12-22 | 2010-05-25

How are you calculating the end dates? edit: I think I see; is the third EndDate supposed to be 2010-12-20, rather than 2010-08-20? — Bleaourgh, Mar 23 '11 at 17:13
What platform are you targetting, James? e.g. SQL Server, MySQL? — Mike Goatly, Mar 23 '11 at 17:18

score 6 · Accepted Answer · answered Mar 23 '11 at 17:19

6

I will not put here the ID as I see it is irrelevant in the query. If you wish you will put it later. This is a MSSQL query.

select tb1.date as startdate,dateadd(d,-1,tb2.date) as enddate
from the_table tb1
join the_table tb2 on tb2.date>tb1.date
left join the_table tb3 on tb1.date<tb3.date and tb3.date<tb2.date
where tb3.date is null

It can be easily translated for other DB types.

answered Mar 23 '11 at 17:19

Dumitrescu Bogdan

7,127
2
23
31

This is actually a really useful approach for a couple of scenarios. Very cool. – Mike Goatly Mar 25 '11 at 11:09
very old post.. but thought I would add a couple things: the OP has a date in 2011 in the first table, which i think should be the end date of the last record in the second table. Also, this would also imply that you would wawnt an additional record that starts on the 2011 date, and hasn't ended yet. for that, use this answer but union it with the currently active records eg. union select tbl1.date, null from (select date, max(date) over (partition by id) current from the_table) tbl1 where date = current (note i've assumed we want to do this per ID) – da Bich Nov 13 '22 at 16:11

score 1 · Answer 2 · edited May 23 '17 at 12:07

1

These two links will give you a rough idea.

edited May 23 '17 at 12:07

Community

1
1

answered Mar 23 '11 at 17:17

Saurabh Gokhale

53,625
36
139
164

score 1 · Answer 3 · answered Mar 23 '11 at 17:25

if you have serialize id like ( 1,2,3,4,5,..) then you can get the above with this query with 1 extra data at last but you can omit that the folloowing is the mysql query

SELECT startdate, ( select startdate - INTERVAL 1 DAY from tester a where a.id = b.id +1) as enddate FROM tester b

SQL Query Creating Start and End Dates

3 Answers3

Linked