Solved

Count overlapping days between 2 date ranges

Posted on 2004-10-01
3
2,187 Views
Last Modified: 2012-05-08
Hi,

I'm trying to create a query that counts the overlapping days between 2 date ranges from a view.

For example take this range in the view RESERVATIONS:
startdate | enddate | totaldaycount
2004-01-04 | 2004-01-08 | 4

in the query should look something like this:

SELECT @startdate = '2004-01-02';
SELECT @enddate =  '2004-01-06';
SELECT overlappingdays = ( ...?... ) FROM RESERVATIONS  
  WHERE (startdate BETWEEN @startdate +1 AND @enddate -1)
    OR (enddate BETWEEN @startdate +1 AND @enddate -1)
    OR (startdate <= @startdate AND enddate >= @enddate)

the resulting integers from the following ranges should be:
@startdate | @enddate | @result
2004-01-02 | 2004-01-06 | 2
2004-01-04 | 2004-01-08 | 4
2004-01-06 | 2004-01-10 | 2
2004-01-02 | 2004-01-10 | 4

I don't want to use function (I know how to get the result like that), preferably a pure select query.

Thanks!

Wiebe
0
Comment
Question by:Uritsukidoji
3 Comments
 
LVL 15

Expert Comment

by:justinbillig
ID: 12201532
i dont get what you mean by overlapping days
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 12201775
Please try this:


SELECT overlappingdays = DATEDIFF(DAY,
      CASE WHEN startDate > @startDate THEN startDate ELSE @startDate END,
      CASE WHEN endDate < @endDate THEN endDate ELSE @endDate END)
FROM ...
0
 
LVL 3

Author Comment

by:Uritsukidoji
ID: 12203654
Scott, it works fine!

Thanks a lot,

Wiebe
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question