Sign in to follow this  
Followers 0

8 posts in this topic

Posted

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

Posted

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

Share this post


Link to post
Share on other sites

Posted

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

Posted

@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

Posted

@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

Posted

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

Posted

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.