Jump to content



Photo

SQL Query to Find Consecutive Dates

sql 2005 sql server

  • Please log in to reply
7 replies to this topic

#1 limok

limok

    Neowinian

  • Joined: 20-May 04
  • Location: Manchester, UK
  • OS: Windows 8.1
  • Phone: Galaxy S4, iPhone 5

Posted 25 June 2013 - 09:09

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,

 




#2 spikey_richie

spikey_richie

    There's no place like 127.0.0.1

  • Tech Issues Solved: 1
  • Joined: 02-February 05
  • Location: Nuneaton, UK
  • OS: Windows 8.1 Pro 64-bit
  • Phone: Sony Xperia Z1

Posted 25 June 2013 - 09:15

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

 

http://www.sqlteam.c...s-in-sql-server



#3 OP limok

limok

    Neowinian

  • Joined: 20-May 04
  • Location: Manchester, UK
  • OS: Windows 8.1
  • Phone: Galaxy S4, iPhone 5

Posted 25 June 2013 - 09:33

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

 

http://www.sqlteam.c...s-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



#4 ramesees

ramesees

    Neowinian Senior

  • Joined: 02-October 01
  • OS: Windows Server 2008 R2

Posted 25 June 2013 - 10:00

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

 

 

 



#5 OP limok

limok

    Neowinian

  • Joined: 20-May 04
  • Location: Manchester, UK
  • OS: Windows 8.1
  • Phone: Galaxy S4, iPhone 5

Posted 25 June 2013 - 11:40

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



#6 ramesees

ramesees

    Neowinian Senior

  • Joined: 02-October 01
  • OS: Windows Server 2008 R2

Posted 25 June 2013 - 12:48

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



#7 OP limok

limok

    Neowinian

  • Joined: 20-May 04
  • Location: Manchester, UK
  • OS: Windows 8.1
  • Phone: Galaxy S4, iPhone 5

Posted 25 June 2013 - 12:59

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


#8 OP limok

limok

    Neowinian

  • Joined: 20-May 04
  • Location: Manchester, UK
  • OS: Windows 8.1
  • Phone: Galaxy S4, iPhone 5

Posted 25 June 2013 - 13:08

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





Click here to login or here to register to remove this ad, it's free!