• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2760
  • Last Modified:

Count overlapping days between 2 date ranges

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
Uritsukidoji
Asked:
Uritsukidoji
1 Solution
 
justinbilligCommented:
i dont get what you mean by overlapping days
0
 
Scott PletcherSenior DBACommented:
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
 
UritsukidojiAuthor Commented:
Scott, it works fine!

Thanks a lot,

Wiebe
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now