SQL Query Start and End Date

Question

This is slightly different than this: SQL Query Creating Start and End Dates

And I do not know how to phrase this question as I suck in English.

Table

Name | Date
Foo | 1-Sep
Foo | 2-Sep
Foo | 3-Sep
Foo | 5-Sep
Foo | 6-Sep
Foo | 7-Sep

Foo | 1-Dec
Foo | 2-Dec

Foo | 4-Dec
Foo | 5-Dec
Foo | 6-Dec
Foo | 7-Dec
Foo | 8-Dec

Foo | 1-Feb

Foo | 14-Feb
Foo | 15-Feb
Foo | 16-Feb
Foo | 17-Feb
Foo | 18-Feb
Foo | 19-Feb
Foo | 20-Feb

Foo | 22-Feb

I need to create a query with the below result

Query Result

Name | Start Date  |  End Date
Foo  |  1-Sep      | 3-Sep
Foo  |  5-Sep      | 7-Sep
Foo  |  4-Dec      | 8-Dec
Foo  |  1-Feb      | 1-Feb
Foo  |  1-Dec      | 2-Dec
Foo  |  14-Feb     | 20-Feb
Foo  |  22-Feb     | 22-Feb

Update:

Start Date: First day of a series of consecutive days
End Date: Last day of a series of consecutive days
If a date is not included in a series of consecutive days, like Feb 22 above, it is the start and end date.

Explain us why this output is correct, then we can help you with query — Piotr Auguscik, Sep 02 '11 at 08:35
You should explain how you arrive at those end dates, especially 8-sep. — Hans Keﬆing, Sep 02 '11 at 08:36
@Anon, which version of SQL (Oracle, SQLServer, MySQL, PostgreSQL, etc) are you using? Date manipulation differs significantly between SQL flavours. — , Sep 02 '11 at 08:48

score 0 · Answer 1 · edited May 23 '17 at 11:55

0

Not sure if its the best way. You can first filter all dates that have previous and next day, then you can use solution explained in here

Query that filters unimportant dates:

select t1.Name, t.Date from Table t1
left outer join Table t2 on dateadd(d,-1,t1.Date) = t2.Date 
left outer join Table t3 on dateadd(d,1,t1.Date) = t3.Date
where t2.Date is null or t3.Date is null

edited May 23 '17 at 11:55

Community

1
1

answered Sep 02 '11 at 08:50

Piotr Auguscik

3,651
1
22
30

SQL Query Start and End Date

1 Answers1