Min Ko Ko Min Ko Ko - 3 months ago 16
MySQL Question

hotel booking check empty room between arrival date and departure date?

Basically i retrieve all available room with arrival and departure date from the room table.

SQL Query

SELECT * FROM `roomcalendar` where day BETWEEN '2016-08-25' and '2016-08-31'


Result is

id day roomname avaroom
1 2016-08-25 RoomA 0
2 2016-08-26 RoomA 2
3 2016-08-27 RoomA 0
4 2016-08-28 RoomA 1
5 2016-08-29 RoomA 1
6 2016-08-30 RoomA 0
7 2016-08-31 RoomA 1


I just want available room if avaroom not equal to 0 beteen arrival date and departure date

And I want the result

SQL Query

SELECT * FROM `roomcalendar` where day BETWEEN '2016-08-25' and '2016-08-31'


it must be empty result

SQL Query

SELECT * FROM `roomcalendar` where day BETWEEN '2016-08-25' and '2016-08-26'


it must be empty result

SQL Query

SELECT * FROM `roomcalendar` where day BETWEEN '2016-08-26' and '2016-08-27'


it show one result

SQL Query

SELECT * FROM `roomcalendar` where day BETWEEN '2016-08-26' and '2016-08-28'


it must be empty result

SQL Query

SELECT * FROM `roomcalendar` where day BETWEEN '2016-08-28' and '2016-08-30'


it show two result

Thanks.

Answer

I just want available room if avaroom not equal to 0 beteen arrival date and departure date

So if you reformulate this:

  • you want the room if there is no row where avaroom = 0 between two dates.

Translated in SQL:

select * from roomcalendar
where day between '2016-08-25' and '2016-08-31'
and not exists (
  select * from roomcalendar
  where day between '2016-08-25' and '2016-08-31'
  and avaroom = 0
);

You can also reformulate by saying:

  • you want the room if the number of rows where avaroom is not 0 is the total number of rows expected (in this case, this corresponds to the amount of days)

Translated in SQL:

select * from roomcalendar
where day between '2016-08-25' and '2016-08-31'
and avaroom != 0
having count(*) = datediff('2016-08-31','2016-08-25') + 1

You might have to tweak the queries a bit, but that should point you to the right direction.

Comments