SQL Query to Find Consecutive Dates

[limok]

Hi,

 

I'm trying to develop a query that world count consecutive days from a list of from and to dates. Consider the below dates:

 

FROMDATE UNTILDATE
2013-08-12  2013-08-14
2013-08-15  2013-08-16
2013-08-19  2013-08-23
2013-08-27  2013-08-27

 

As you can see the first two rows are consecutive and total 5 days.

 

Please can you help!

 

Thanks,

 

[spikey_richie]

I don't know the answer, but I did find this which might help:

 

http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server

[limok]

I don't know the answer, but I did find this which might help:

 

http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server

 

Good read but doesn't show how to select multiple rows that are consecutive. Just shows durations for single row.

 

Thanks anyway

[ramesees]

create table #tempDates

(

     Id int identity(1,1),

     StartDate datetime,

     EndDate datetime

);

 

insert into #tempDates values ('2013-08-12', '2013-08-14')

insert into #tempDates values ('2013-08-15', '2013-08-16')

insert into #tempDates values ('2013-08-19', '2013-08-23')

insert into #tempDates values ('2013-08-27', '2013-08-27')

 

select td1.StartDate,

       td2.EndDate,

       datediff(d, td1.StartDate, td2.EndDate) as Days

from #tempDates td1 left join

     #tempDates td2 on td1.Id <> td2.Id

where (td2.StartDate = DateAdd(d, 1, td1.EndDate)) OR (td2.StartDate = DateAdd(d, 0, td1.EndDate))

 

drop table #tempDates

 

 

This gives the following:

 

StartDate                   EndDate                 Days

----------------------- ----------------------- -----------

2013-08-12 00:00:00.000 2013-08-16 00:00:00.000 4

 

 

 

[limok]

@Ramesees

 

Getting alot of repeated data, and for some reason I can't filter the data using where clauses? for example my ideneity column is being displayed multiple times?

[ramesees]

@limok can you post a sample of your data and I'll take a look

[limok]

Yes sure

 

I'm sorry I shouldve mentioned that it should be per employee that can have many REFs

REF    FROMDATE                     UNTILDATE                     Days
12778 2012-03-27 00:00:00.000 2012-03-30 00:00:00.000 3
12778 2012-03-27 00:00:00.000 2012-03-27 00:00:00.000 0
12778 2012-03-27 00:00:00.000 2012-03-27 00:00:00.000 0
12778 2012-03-27 00:00:00.000 2012-03-30 00:00:00.000 3
12778 2012-03-27 00:00:00.000 2012-03-30 00:00:00.000 3
12778 2012-03-27 00:00:00.000 2012-03-28 00:00:00.000 1
12778 2012-03-27 00:00:00.000 2012-03-30 00:00:00.000 3
12778 2012-03-27 00:00:00.000 2012-03-28 00:00:00.000 1

[limok]

I've just added a join on employee = employee and going to test the data.