• 0

SQL Query to Find Consecutive Dates


Question

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,

 

Link to comment
https://www.neowin.net/forum/topic/1160902-sql-query-to-find-consecutive-dates/
Share on other sites

7 answers to this question

Recommended Posts

  • 0
  On 25/06/2013 at 09:15, spikey_richie said:

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

  • 0

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

 

 

 

  • 0

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
This topic is now closed to further replies.
  • Posts

    • -- "So, how could the Trump team make such a mistake on something so politically important to them?" Because the Trump team didn't care enough about editing the embedded map on the website just like they didn't care enough about actually building a phone. They're just slapping Trump's name on some pre-built Chinese OEM Android phone and selling it to MAGA rubes. 🤣
    • Here in the UK we used to have a mobile network that would've been perfect for Trump. It was called ... Orange!
    • Trump's team takes down Trump Mobile coverage map that includes "Gulf of Mexico" by David Uzondu Image via Depositphotos.com When Donald Trump returned to office in January this year, one of the first things he did was sign an executive order to officially rename the Gulf of Mexico to the "Gulf of America". It was a signature move, but it seems his own team might have forgotten about it. The rollout for the new Trump Mobile service, a venture headed by his sons, is already facing issues with its pre-orders, and now has a whole new self-inflicted problem to deal with. A post from Travis Akers (@travisakers) has shown that the Trump Mobile official coverage map, intended to show potential customers their signal strength, prominently featured the name "Gulf of Mexico." It appears shortly after Akers's post went live, the Trump team took down the page, so if you visit trumpmobile.com/coverage, you would get a 404 error. Anyways, here's a screen recording of what the page looked like before the take down (double click to enlarge): So, how could the Trump team make such a mistake on something so politically important to them? From the recording, it looks like the coverage map is powered by Mapbox, a popular location platform for developers. Mapbox, by default, still uses "Gulf of Mexico" because its core map data is built on global sources like OpenStreetMap, which have not adopted the unilateral name change. Even though the official name is different in the US, these global datasets stick to the international consensus. Other tech companies, like Google, address this by changing their maps to display "Gulf of America" only for users in the US, a decision that annoyed the president of Mexico. The funny thing is, fixing this would have been pretty simple. Mapbox is quite flexible, and you can edit "natural features", including bodies of water. Here's how we did it (click to enlarge): The Trump Mobile coverage map has been offline for over five hours, as the Trump team presumably works to fix the issue and bring the page back online.
    • Maybe stop using that ###### once and for all? One can live withouth it. Anything META is cancer.
    • This also highlights the dangers of giving corporations unencrypted access to your documents / photos. It would appear this persons data has likely been scanned by Ai, which found something it didn't like, locking access to the account.
  • Recent Achievements

    • Week One Done
      rozermack875 earned a badge
      Week One Done
    • Week One Done
      oneworldtechnologies earned a badge
      Week One Done
    • Veteran
      matthiew went up a rank
      Veteran
    • Enthusiast
      Motoman26 went up a rank
      Enthusiast
    • Mentor
      M. Murcek went up a rank
      Mentor
  • Popular Contributors

    1. 1
      +primortal
      684
    2. 2
      ATLien_0
      266
    3. 3
      Michael Scrip
      194
    4. 4
      +FloatingFatMan
      177
    5. 5
      Steven P.
      140
  • Tell a friend

    Love Neowin? Tell a friend!