Solved

Count overlapping days between 2 date ranges

Posted on 2004-10-01
3
2,099 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:
ScottPletcher 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now