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 5 · Accepted Answer · answered Mar 23 '11 at 17:19

5

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

6,842
2
22
31

This is actually a really useful approach for a couple of scenarios. Very cool. – Mike Goatly Mar 25 '11 at 11:09

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

51,864
34
134
163

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