Request: Calculate average price per stay based on date

Question

Hello to all,

I need to create a search query for a house that matches the data entered in the database: the date they want to move and leave, the number of people they have in the group, and the price per night.

Let's say the user was looking for a house:

dates: from 2011-01-15 to 2011-03-01 (see image period A1C1), for 3 people, and he is willing to spend from 90 to 125 US dollars per night.

These are my manual calculations for this search:

• dates available in the database
• Total dates the user wants to stay: 44 days
• The price for the first period 2011-01-15 to 2011-01-25 is 10 days * $ 100 = $ 1000
• The price for the second period 2011-01-25 to 2011-02-14 is 20 days * $ 120 = $ 2400
• The price for the third period 2011-02-14 to 2011-03-01 is 14 days * $ 140 = $ 1960
• total average price per night = 1000 + 2400 + 1960/44 = $ 121.8
• the price and number of people matches user input, so we show this house

If you combine the dates and calculate the average price per night for a given period, the search script should match the data array above.

My question is: What should my query look like in order to quickly calculate if user data matches records in the database .

I thought about using the SQL DATEDIFF function and then multiplied for the price ... etc, but it looks pretty complicated.

I would be grateful for any advice.

thanks

UPDATE

Here is my database schema:

Apt_search_periods table that stores all the combined dates (continuous dates from the availability table)

+-----------+------------+------------+-----------+--------------+--------+ | period_id | start_date | end_date | rental_id | nb_of_people | merged | +-----------+------------+------------+-----------+--------------+--------+ | 21 | 2011-03-31 | 2012-03-31 | 548 | 4 | y | +-----------+------------+------------+-----------+--------------+--------+ 

Apt_search_periods_avail table associated with merged dates with availability table

 +----+-----------+-----------------+ | id | period_id | availability_id | +----+-----------+-----------------+ | 21 | 21 | 20953 | | 22 | 21 | 20952 | | 23 | 21 | 4033 | +----+-----------+-----------------+ 

Availability table with advanced dates and prices

 +-------+-----------+------------+------------+--------------+--------------+ | id | rental_id | start_date | end_date | nb_of_people | rent_per_day | +-------+-----------+------------+------------+--------------+--------------+ | 20952 | 548 | 2011-03-31 | 2011-07-01 | 4 | 575 | | 4033 | 548 | 2011-07-01 | 2011-09-01 | 4 | 680 | | 20953 | 548 | 2011-09-01 | 2012-03-31 | 4 | 575 | +-------+-----------+------------+------------+--------------+--------------+ 

Answer 1

After that you should start.

Please note that the only difference is that the third period includes 15 days io 14 according to DATEDIFF.

SQL statement

 ;WITH q AS ( /* Kick of with the record where startdate < input < enddate */ SELECT date_start , date_end FROM @HouseSearch WHERE date_start <= @date_start AND date_end >= @date_start AND nb_people >= @nb_people -- Only when number of people is adequate UNION ALL SELECT q.date_start , hs.date_end FROM q INNER JOIN @HouseSearch hs ON hs.date_start = q.date_end WHERE nb_people >= @nb_people -- Only when number of people is adequate ) SELECT * FROM ( -- Only return result if sequence exists between date range SELECT date_start = MIN(date_start) , date_end = MAX(date_end) FROM q WHERE date_end >= @date_end ) datetimerange -- Calculate the average price CROSS APPLY ( SELECT [AveragePrice] = SUM(price / DATEDIFF(dd, @date_start, @date_end)) FROM ( -- Price for all records where date_end <= @date_end SELECT [price] = CASE WHEN @date_start < date_start THEN DATEDIFF(dd, date_start, date_end) * price ELSE DATEDIFF(dd, @date_start, date_end) * price END FROM @HouseSearch WHERE @date_end > date_end UNION ALL -- Price of remaining records where date_end >= @date_end SELECT DATEDIFF(dd, date_start, @date_end) * price FROM @HouseSearch WHERE @date_end between date_start AND date_end ) prices ) price WHERE date_start IS NOT NULL 

Test Data

 DECLARE @HouseSearch TABLE ( date_start DATE , date_end DATE , nb_people INTEGER , price FLOAT ) INSERT INTO @HouseSearch VALUES ('2011-01-01', '2011-01-25', 4, 100) , ('2011-01-25', '2011-02-14', 3, 120) , ('2011-02-14', '2011-03-12', 3, 140) , ('2011-03-12', '2011-04-10', 3, 100) DECLARE @date_start DATE = '2011-01-15' DECLARE @date_end DATE = '2011-03-01' DECLARE @nb_people INTEGER = 3 DECLARE @price_low FLOAT = 90 DECLARE @price_high FLOAT = 15