Sign in to follow this  
Followers 0
limok

SQL Query to Find Consecutive Dates

8 posts in this topic

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,

 

Share this post


Link to post
Share on other sites

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

 

 

 

Share this post


Link to post
Share on other sites

@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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.