0

I am developing a system to enable booking of rooms on one hour basis. I have the following tables: Bookings Rooms Slots Days

Days Table Contains all days in a year. (Below is just a snapshot of days in June)

+-----------+------------+--------------------+
| id        |  date_day  | created_at         |
+-----------+------------+------------+-------+
|         1 | 2017-06-20 |2017-06-26 22:05:11 |
|         2 | 2017-06-21 |2017-06-26 22:05:12 |
|         3 | 2017-06-22 |2017-06-26 22:05:20 |
|         4 | 2017-06-23 |2017-06-26 22:05:24 |
|         5 | 2017-06-24 |2017-06-26 22:05:28 |
|         6 | 2017-06-25 |2017-06-26 22:05:31 |
|         7 | 2017-06-26 |2017-06-26 22:05:35 |
|         8 | 2017-06-27 |2017-06-26 22:05:40 |
+-----------+------------+------------+-------+

Rooms Table

+-----------+------------+------------+--------------------+
| id        |  name      | status     | created_at         |
+-----------+------------+------------+--------------------+
|         1 | Room A     | 1          |2017-06-26 22:16:21 |
|         2 | Room B     | 1          |2017-06-26 22:21:45 |
+-----------+------------+------------+--------------------+

Slots Table

+-----------+------------+------------+--------------------+
| id        | start_time | end_time   | created_at         | 
+-----------+------------+------------+--------------------+
|     1     | 07:00 AM   | 08:00 AM   |2017-06-26 20:22:45 |   
|     2     | 08:00 AM   | 09:00 AM   |2017-06-26 20:28:08 |   
|     3     | 09:00 AM   | 10:00 AM   |2017-06-26 20:36:27 |   
|     4     | 10:00 AM   | 11:00 AM   |2017-06-26 20:44:12 |   
+-----------+------------+------------+--------------------+

Bookings Table

+-----------+------------+------------+-------------+--------------------+
| id        | room_id    | slot_id    |booked_date  | created_at         |
+-----------+------------+------------+-------------+--------------------+
|1          | 1          | 1          |7            |2017-06-26 22:16:08 |
|2          | 1          | 2          |7            |2017-06-26 22:18:26 |
|3          | 1          | 3          |7            |2017-06-27 05:17:35 |
|4          | 1          | 4          |7            |2017-06-27 06:43:04 |
|5          | 1          | 1          |7            |2017-06-27 08:22:48 |
+-----------+------------+------------+-------------+--------------------+

I want to get booking data for a certain room within a range of one week as follows:

Booking Data for Room A:

+-----------+-----------+-----------+-----------+-----------+
|date       |07:00-08:00|08:00-09:00|09:00-10:00|10:00-11:00|
+-----------+-----------+-----------+-----------+-----------+
|2017-06-26 | Booked    | Booked    | Booked    | Booked    |
|2017-06-27 | Booked    | Available | Available | Available |
+-----------+-----------+-----------+-----------+-----------+

Am using the following query but it is not giving me the correct data:

SELECT tbl_days.id,tbl_days.date_day,
       IF(tbl_bookings.slot_id =1,'Booked','Available') AS '07:00 to 08:00',  
       IF(tbl_bookings.slot_id =2,'Booked','Available') AS '08:00 to 09:00',  
       IF(tbl_bookings.slot_id =3,'Booked','Available') AS '09:00 to 10:00',  
       IF(tbl_bookings.slot_id =4,'Booked','Available') AS '10:00 to 11:00',  
FROM tbl_days,
LEFT JOIN tbl_bookings 
     ON tbl_bookings.booking_date = tbl_days.id,
LEFT JOIN tbl_slots 
     ON tbl_slots.id = tbl_bookings.slot_id,
LEFT JOIN tbl_fields 
     ON tbl_rooms.id = tbl_bookings.room_id

However, this is not giving me the intended data. Will appreciate tips on what I am doing wrong and could do right.

0

Finally got a hook of it by using MAX (CASE WHEN.. END) instead of IF

SELECT tbl_days.id,tbl_days.date_day,
       MAX(CASE WHEN tbl_bookings.slot_id =1 THEN 'Booked' ELSE 'Available' END) '07:00 to 08:00',  
       MAX(CASE WHEN tbl_bookings.slot_id =2 THEN 'Booked' ELSE 'Available' END) '08:00 to 09:00',  
       MAX(CASE WHEN tbl_bookings.slot_id =3 THEN 'Booked' ELSE 'Available' END) '09:00 to 10:00',  
       MAX(CASE WHEN tbl_bookings.slot_id =4 THEN 'Booked' ELSE 'Available' END) '10:00 to 11:00'  
FROM tbl_days
LEFT JOIN tbl_bookings 
     ON tbl_bookings.day_date = tbl_days.id
LEFT JOIN tbl_slots 
     ON tbl_slots.id = tbl_bookings.slot_id
LEFT JOIN tbl_rooms 
     ON tbl_rooms.id = tbl_bookings.room_id
GROUP BY tbl_days.id;
0

Try this:

SELECT tbl_rooms.name,tbl_days.date_day,
 MAX( IF(tbl_bookings.slot_id =1,'Booked','Available') )AS '07:00 to 08:00',  
      MAX( IF(tbl_bookings.slot_id =2,'Booked','Available')) AS '08:00 to 09:00',  
       MAX(  IF(tbl_bookings.slot_id =3,'Booked','Available') )AS '09:00 to 10:00',  
       MAX(  IF(tbl_bookings.slot_id =4,'Booked','Available') )AS '10:00 to 11:00'  
FROM tbl_bookings
INNER JOIN tbl_rooms 
     ON tbl_bookings.room_id =tbl_rooms.id
LEFT JOIN
tbl_days
     ON tbl_bookings.booked_date = tbl_days.id
LEFT JOIN tbl_slots 
     ON tbl_slots.id = tbl_bookings.slot_id

GROUP BY tbl_rooms.name,tbl_days.date_day;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.