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
LVL 3
UritsukidojiAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott PletcherConnect With a Mentor Senior 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
 
justinbilligCommented:
i dont get what you mean by overlapping days
0
 
UritsukidojiAuthor Commented:
Scott, it works fine!

Thanks a lot,

Wiebe
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.