mysql - Hotel Booking Rates SQL Problem -


Hello, after reading other questions here on stack overflow, try to get your own system to check room rates I am doing

My query works fine and produces correct dates when there is no overlap in reservation dates, but when one overlaps, I get an extra day on my second row of results .

For example one person arrives on 2011-04-14 and leaves 2011-04-16 (2 days). Rates vary between 66 to 70 at the rate of 15, so it should be at 1 day 66 and at 1 day 70.

I have tried without time - just like this on DATE.

query 

  SELECT rates.rate_id, rate_start_date, rate_end_date, rate_price, (DATEDIFF (IF (rate_end_date> '2011-04-16 14:00:00' , '2011-04-16 14:00:00', rate_and_date), IF (rate_start_date) '2011-04-14 12:00:00', '2011-04-14 12:00:00', rate_start_date )) +1) by WHERE rate_start_date at rates of days & lt; = '2011-04-16 14:00:00' and rate_and_date & gt; Rate As per APC ASC   
 Order table  
  rate_rate rate_stat_data rate_and_date170 2011-04 according to order '2011-04-14 12:00:00' -15 00:00:00 2011-05-31 23:59:59 2 80 2011-06-01 00:00:00 2011-06-30 23:59:59 3 100 2011-07-01 00:00: 00 2011 -08-31 23:59:59 4 80 2011-09-01 00:00:00 2011-09-30 23:59:59 5 70 2011-10-01 00:00:00 2011-10-31 23: 59:59 6 45 2011-11-01 00:00:00 2011-12-31 23:59:59 0 66 2011-01-01 00:00:00 2011-04-14 23:59:59 < / Code>  
 Result  
  rate_id rate_start_date rate_end_date daydays 01-01-01 00:00:00 2011-04-14 23:59:59 66 1 1 2011- 04-15 00:00:00 2011-05-31 23:59:59 70 2 & lt; ---- It should be 1 day   
 I really want any help or someone why my query tells me extra day of second row of results.  
 Thanks   
 
 
 2011-04-16 16:00 and 2011-04- There is a day and 16 hours between 15 00:00, so your  DATEDIFF (...) + 1  is Raining (correctly) 1 + 1 day.  
 The problem here is incompatible during the time when guests arrive / leaves (almost noon) and changes in that time (midnight).  
 You have to check your requirements, but you can probably ignore the previous partial day and guests can "leave" 2011-04-15 23:59:59 for rate calculation purposes. . In the same way, keep the guest on "arrival" at 2011-04-14 00:00:00 Additional hours of the first day will cover the missing hours on the last day.

Comments

Post a Comment