SQL question

[tonyyeb]

Here is a tricky question...

I have a table full of dates in a SQL database. Here is an example

ID Start Date  End Date
1  01/01/2010 01/01/2010
2  10/03/2010 14/10/2010
3  04/04/2010 04/04/2010
4  09/06/2010 09/06/2010
5  12/11/2010 30/11/2010
6  03/12/2010 03/12/2010
7  04/12/2010 04/12/2010
8  05/12/2010 06/12/2010

What I would like is to be able to say something like:

There are 3 x 1 single days
There are 2 x 4 days
There are 1 x 18 days

So I am grouping the periods. Is there a SQL command/function/query that would do this... taking into account the last three records which following each other chronologically with a sequence of 1 single say, 1 single day, 2 days.

Thanks for ANY help!!

 

[tonyyeb]

I'm not sure if i've made this clear enough. Yes I can calculate the difference in the start and end date of each record. The problem comes when you have for example three records which follow each other. Such as:

Start Date  End Date
01/01/2010 01/01/2010
02/01/2010 02/01/2010
03/01/2010 03/01/2010

I would want to count this as 1 x 3 days not 3 x 1 days.

EDIT: Using SQL Server 2005.

 

[tonyyeb]

@Teq

Only part of the problem has been solved.

To fix it I have had to add another field which includes a period ID so that groups of individual days that run sequentially and chronologically, are seen as one period.

@all

Thanks for your efforts. Looks like this one wouldn't be simple to solve using just SQL (and without modifying the table!)