MySQL – How to Join to Show Room Booking Availability by Slot

MySQL

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.

Best Answer

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;

Related Solutions

Mysql – How to join two table and show one query result in MySQL

Here is the query you need:

SELECT name FROM
(
    SELECT name FROM table1
    UNION
    SELECT name FROM table2
) A;

Here is some sample code based on your question:

use test
drop table if exists table1;
drop table if exists table2;
create table table1
(
    id int not null auto_increment,
    name varchar(10),
    primary key (id)
);
create table table2 like table1;
insert into table1 (name) values ('A'),('B'),('C');
insert into table2 (name) values ('C'),('D'),('E');
SELECT name FROM
(
    SELECT name FROM table1
    UNION
    SELECT name FROM table2
) A;

Here is the execution of that sample code:

mysql> drop table if exists table1;
Query OK, 0 rows affected (0.03 sec)

mysql> drop table if exists table2;
Query OK, 0 rows affected (0.03 sec)

mysql> create table table1 (
    ->     id int not null auto_increment,
    ->     name varchar(10),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> create table table2 like table1;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into table1 (name) values ('A'),('B'),('C');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into table2 (name) values ('C'),('D'),('E');
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT name FROM (SELECT name FROM table1
    -> UNION SELECT name FROM table2) A;
+------+
| name |
+------+
| A    |
| B    |
| C    |
| D    |
| E    |
+------+
5 rows in set (0.00 sec)

mysql>

Give it a Try !!!

MySQL – Optimal Table Design for Medical Center Rental Service and Recurrent Shifts

You could have a bookings table like this:

bookings
--------
  id (PK)
  user_id (FK to table with all medical professionals)
  room_id (FK to table with all rooms)
  start_date - datetime value
  end_date - datetime value

This structure will let you track which professional is using which room, and when:

bookings
--------
id  | user_id  | room_id  | start_date    | end_date
---------------------------------------------------------
1   | 001      | 123      | 20140101T1030 | 20140101T1045
2   | 001      | 123      | 20140101T1100 | 20140101T1115
3   | 002      | 120      | 20140101T1000 | 20140101T1100

To track historic data, you'll probably want to use "soft" deletes on rooms and professionals so that even if you decide to delete Dr. Phillips from the list of current renters, you can still look up when he used which rooms. Your application will have to be smart enough to not create new bookings with professionals/rooms that are marked as "deleted".

professionals
-------------
  id
  name
  deleted
  (other fields...)

rooms
-----
  id
  deleted
  (other fields)

Example:

professionals
-------------
id  | name       | deleted
----+------------+--------
1   | Dr. Philips| N
2   | Dr. Nick R.| Y

rooms
-----
id  | deleted
----+--------
123 | N
120 | N
837 | Y

To handle recurring appointments, you could start with something like this:

recurring_bookings
------------------
  id (PK)
  user_id (FK to med. professionals)
  room_id (FK to rooms)
  day_of_week (could be FK to table/enum of days of week)
  start_time
  end_time (or you could have a "duration", in minutes, if you prefer)
  start_of_recurrence
  end_of_recurrence

recurring_bookings
------------------
id   | user_id    | room_id   | day_of_week | start_time | end_time | start_of_recurrence | end_of_recurrence
-----+------------+-----------+-------------+------------+----------+---------------------+------------------
1    | 001        | 120       | MONDAY      | 1030       | 1100     | 2014-01-01          | 2014-12-31
2    | 001        | 120       | WEDNESDAY   | 1030       | 1100     | 2014-01-01          | 2014-12-31

The above example shows a user with a recurring booking every monday and wednesday in room 120, running from 10:30 to 11:00 AM. It also shows that the period over which both recurrences are defined runs from 2014-01-01 to 2014-12-31

The tricky part is handling exceptions. For example, suppose a user wants to eliminate only one instance of a recurring booking, because they'll be on vacation that week. One strategy might to think of the recurring bookings table as a rule-set which can be used to populate real bookings into the main bookings table. So that way, you use recurring_bookings to populate all appointments for a user in bookings, and then rmeove or modify them from bookings as needed. This requires you to pre-populate a lot of data into your main bookings table (based on start_of_recurrence and end_of_recurrence), which you may or may not want to do, for recurring appointments. To handle open-ended recurring appointments, it might require a bit more work. Also, the above example assumes all recurring bookings are weekly. Some of them may be daily or yearly or monthly, and may have conditions such as "first Wednesday of every month".

I will admit that I haven't tried to build a system with recurring bookings like this, so this design should be considered a suggestion for a starting point. It will probably need some work. To get an idea of one way this is done, try creating and then manipulating recurring appointments in Outlook (if you have access to it).

Best Answer

Related Solutions

Mysql – How to join two table and show one query result in MySQL

MySQL – Optimal Table Design for Medical Center Rental Service and Recurrent Shifts

Related Question