I worked in travel CRS protal. for hotel booking i want to block hotel depends on the cutoff dates.
SELECT * FROM (`crs_service_supplier_details` as crs) JOIN `crs_service_room_type_detail` as Room ON `crs`.`service_apartment_id`=`Room`.`service_id` JOIN `crs_room_type` as crt ON `Room`.`room_type_id`=`crt`.`room_type_id` JOIN `crs_service_off_dates` as ccod ON `crs`.`service_apartment_id`=`ccod`.`co_service` WHERE `crs`.`city` like '%Chennai%' AND ('2016-10-27' NOT between ccod.cut_off_from AND ccod.cut_off_from) AND ('2016-10-28' NOT between ccod.cut_off_to AND ccod.cut_off_to) AND `Room`.`edate` > '2016-10-27' GROUP BY `crs`.`service_apartment_id`
this is the query but the problem was in cut had two days
co_id co_service cut_off_from cut_off_to cut_off_release_days cut_off
1 1 2016-10-20 2016-10-22 0 2016-10-20
2 1 2016-10-27 2016-10-28 0 2016-10-28
if i searched between 2016-10-20 and 2016-10-22 it get the "2016-10-27 and 2016-10-28" and show the result.
if i searched between 2016-10-27 and 2016-10-28 it get the "2016-10-20 and 2016-10-22" and show the result. it wont block any where of the system. please give some idea to solve this?
May be your query is not right. May be this will help, just change in BETWEEN logic:
AND ('2016-10-27' NOT BETWEEN ccod.cut_off_from AND ccod.cut_off_to)
AND ('2016-10-28' NOT BETWEEN ccod.cut_off_to AND ccod.cut_off_from)
use the above.
The original code with BETWEEN doesn`t make sense:
AND ('2016-10-27' NOT BETWEEN ccod.cut_off_from AND ccod.cut_off_from)
AND ('2016-10-28' NOT BETWEEN ccod.cut_off_to AND ccod.cut_off_to)
Because it is making between on the same column.