查询：根据日期计算平均住宿价格

Question

Hello all,

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

Lets say user searched for house:

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

This is my manual calculations for this search:

• dates available in database
• total number of dates user wants to stay is: 44 days
• price for the first period 2011-01-15 to 2011-01-25 is 10 days * $100 = $1000
• price for the second period 2011-01-25 to 2011-02-14 is 20 days * $120 = $2400
• 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
• price and number of people matches user input, so we display this house

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

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

I was thinking about using SQL DATEDIFF function and then multiply by price ... etc but it looks to me pretty complex.

I will appreciate any advice.

Thank you

UPDATE

Here is my database schema:

Table "apt_search_periods" which stores all merged dates (continuous dates from availability table)

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

Table "apt_search_periods_avail" linking merged dates with availability table

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

Table "availability" with expanded 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

Following should get you started.

Note that the only difference is that the third period comprises 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