CODEWITHSUNDEEP
mysqlsqlselect
Wilf
Wilf

Reputation: 2315

MySQL : How to select record where begin date and end date between two dates of mysql

I need to select a room rate in which check-in date and check-out date is between a range of date specify. Those rates are named separately according to its conditions. Room costs are depends on the date selected. Here is my code:

rate_eb

rate_name     rate_starts     rate_ends     rate_discount     rate_min_stay
---------------------------------------------------------------------------
Low Season    2013-05-01      2013-10-31    20                3            
High Season1  2013-11-01      2013-12-19    20                4
High Season2  2013-02-21      2013-04-31    20                4
Peak Season   2013-12-20      2014-02-20    20                5

The conditions are:

  1. A booking must be in between rate_starts and rate_ends.
  2. A total of nights stay must be greater or equal to rate_min_stay.
  3. rate_discount is a percentage of discount from a master rate from another table. Saying if a master rate is 100, a rate of 80 will be applied to this booking.

Now, I'd like to get those data from rate_db with a date range - especially for rate_discount. Here's my mySQL:

select rate_discount 
from rate_eb 
where rate_min_stay<='4' 
and reb_starts>='2013-06-19' 
and reb_ends<='2013-06-23'

From the code above. I expect rate_discount=20 from Low Season but all of the rate less than or equal to 4 are selected.

Now, please suggest me the solutions. How I can re-write my code to access the rate_discount between rate_starts and rate_ends.

Upvotes: 1

Views: 4175

Answers (2)

Vivienne
Vivienne

Reputation: 600

I assume that the visitor can enter a period, not just one date. What happens if the startdate of that period is in low season and the enddate in high season? Which rate would you like to see then?

select rate_discount 
from rate_eb 
where rate_min_stay <= abs( datediff( reb_date2, reb_date1 ) )
and reb_date1 between rate_starts and rate_ends
and reb_date2 between rate_starts and rate_ends

I'm assuming that reb_date1 is the startdate the visitor entered, and reb_date2 the enddate.

Upvotes: 2

dillip
dillip

Reputation: 1842

use to_date('dateInString','format') to convert string to date for reb_date1 and reb_date2 and use unquoted number value directly if you are doing a greater than comparision for rate_min_stay

Upvotes: 0

Related Questions