Calculate Price For Overlapping Date Range

Question

I have a simple table named HotelRate

HID  |  START_DATE  |   END_DATE    |   PRICE_PER_DAY
--------------------------------------
1        01/1/2015       10/1/2015       100
1        11/1/2015       20/1/2015       75
1        21/1/2015       30/1/2015       110

what is the most simple way to calculate price for Hotel Room if user queries for Total Price between 5/1/2015 to 25/1/2015.

I have checked :

• How can I query for overlapping date ranges?
• Overlapping date range MySQL

but none of it is making much sense to me.

I have tried a couple queries but those seems like hitting arrow in the blind. Can someone suggest me a simple and elegant way to do it ?

---

@JamesZ

On running the first query i get

start_date end_date   duration    price_per_day
---------- ---------- ----------- -------------
2015-01-01 2015-01-10 5           100
2015-01-11 2015-01-20 9           75
2015-01-21 2015-01-30 4           110

For first range 5 is OK, second range it should be 10, third be 5

How days are calculated : Total no of nights between start & end date, which is same as days difference

05-Jan-15   06-Jan-15   1 Night
06-Jan-15   07-Jan-15   1 Night
07-Jan-15   08-Jan-15   1 Night
08-Jan-15   09-Jan-15   1 Night
09-Jan-15   10-Jan-15   1 Night
10-Jan-15   11-Jan-15   1 Night
11-Jan-15   12-Jan-15   1 Night
12-Jan-15   13-Jan-15   1 Night
13-Jan-15   14-Jan-15   1 Night
14-Jan-15   15-Jan-15   1 Night
15-Jan-15   16-Jan-15   1 Night
16-Jan-15   17-Jan-15   1 Night
17-Jan-15   18-Jan-15   1 Night
18-Jan-15   19-Jan-15   1 Night
19-Jan-15   20-Jan-15   1 Night
20-Jan-15   21-Jan-15   1 Night
21-Jan-15   22-Jan-15   1 Night
22-Jan-15   23-Jan-15   1 Night
23-Jan-15   24-Jan-15   1 Night
24-Jan-15   25-Jan-15   1 Night
               Count : 20 Night

Answer 1

Something like this should do the trick:

declare @startdate date, @enddate date

set @startdate = '20150105'
set @enddate = '20150125'

select
  start_date,
  end_date,
  datediff(
    day, 
    case when @startdate > start_date then @startdate else start_date end, 
    case when @enddate < end_date then @enddate else end_date end) as duration,
  price_per_day
from
  reservation
where
  end_date >= @startdate and
  start_date <= @enddate

This just handles the overlapping ranges with case so that if the reservation start is the correct one to use, it takes it, otherwise the search criteria, and same thing for end date. The days and price are here separate, but you can just multiply them to get the result.

SQL Fiddle: http://sqlfiddle.com/#!3/4027b3/1

Edit, this way to get total sum:

declare @startdate date, @enddate date

set @startdate = '20150105'
set @enddate = '20150125'

select
  sum(datediff(
    day, 
    case when @startdate > start_date then @startdate else start_date end, 
    case when @enddate < end_date then @enddate else end_date end)  
  * price_per_day)
from
  reservation
where
  end_date >= @startdate and
  start_date <= @enddate