• 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

    • I'm wondering if they are doing this as a "backup" in case CISA ceases to exist. It almost did recently due to funding and it's future is shaky. CISA - https://www.cisa.gov/known-exploited-vulnerabilities-catalog Example "CVE-2023-39780" https://www.cve.org/CVERecord?id=CVE-2023-39780 ASUS RT-AX55 Routers OS Command Injection Vulnerability
    • Over regulation is bad. That's why the EU is behind the US. But, it's a good thing the EU stepped in, in this case.
    • Thanks to the EU, Windows 11 is now a little more tolerable.
    • Microsoft will finally stop shoving Edge down your throat, on one condition by David Uzondu Avid Windows users must be familiar with the dirty tactics Microsoft employs to push its Edge browser. It's a song as old as time; remember when Internet Explorer was primarily used as a tool to download Chrome or Firefox because it was the only thing available? Shortly after IE died, Edge inherited that legacy, becoming the browser you often had to use just to get the browser you actually wanted. Even Microsoft knows this: For years, we have endured the relentless pop-ups after updates, third parties being blocked from changing the default browser on Windows 11, banners appearing when you dare visit a competitor's download page, a fake "how to uninstall Edge" guide, and links within Windows apps that just had to open in Edge, regardless of your set preferences. Microsoft has announced it is dialing back some of this aggressive behavior, promising a reprieve from the constant Edge bombardment. But (and it's a pretty big but) this only applies if you're in the EEA. This shift isn't Microsoft suddenly having a profound change of heart and deciding to respect user choice out of the goodness of its heart. No, this is all thanks to the Digital Markets Act, a major EU rule that targets big online platforms, what they call "gatekeepers", because these companies have a huge impact on how the digital market works. So, what is actually changing for users in the EEA? For starters, Microsoft Edge will not prompt you to set it as the default browser unless you actually open it directly, like by clicking its icon on the taskbar. This specific change started rolling out with Edge version 137.0.3296.52. Other Microsoft apps will also stop bugging you to reinstall Edge if you dared to remove it, with updates for this rolling out in June to Windows 10 and 11. And speaking of default browsers, this is where a significant improvement lies. Previously, hitting "Set default" for your browser in Windows was half baked, only grabbing basic web links like http and https and HTML files. Now, if you're in the EEA, setting your default browser will also cover more obscure link types like ftp and "read," plus a wider array of web-related file formats such as .mht, .svg, .xml, and even .pdf files, provided your chosen browser says it can handle them. The Bing app and those Windows Widgets, which previously had a nasty habit of ignoring your browser choice, will also start opening web links in your default browser. Hallelujah. Users in the EEA will also gain the ability to uninstall the Microsoft Store entirely later this year, though apps previously installed from it will still receive updates. Windows Search is also getting an upgrade in the EEA. Right now, searching from the taskbar mostly just sends you to Bing, no matter what browser you use. But for users in the EEA, other apps will be able to plug into Windows Search and show web results too. If an app registers as a web search provider, it'll start working as soon as you install it. You'll also be able to see results from multiple providers in the search interface, not just Bing. The usual scoping tabs will still be there if you want to filter things, but the default view will be more varied. And yes, you'll even be able to reorder the providers in Settings. These changes are already in Windows Insider builds and are expected to roll out to Windows 10 and 11 in early June.
  • Recent Achievements

    • One Year In
      WaynesWorld earned a badge
      One Year In
    • First Post
      chriskinney317 earned a badge
      First Post
    • Week One Done
      Nullun earned a badge
      Week One Done
    • First Post
      sultangris earned a badge
      First Post
    • Reacting Well
      sultangris earned a badge
      Reacting Well
  • Popular Contributors

    1. 1
      +primortal
      172
    2. 2
      ATLien_0
      125
    3. 3
      snowy owl
      123
    4. 4
      Xenon
      118
    5. 5
      +Edouard
      92
  • Tell a friend

    Love Neowin? Tell a friend!