I am needing to access two tables from the existing database: ea_user_settings and ea_appointments
The data in those tables looks like this:
ea_user_settings
id_users_provider working_plan
85 {"sunday":null,"monday":null,"tuesday":{"start":"11:00","end":"19:00","breaks":[{"start":"16:00","end":"17:00"},{"start":"13:00","end":"14:00"}]},"wednesday":null,"thursday":null,"friday":null,"saturday":null}
The breaks variable ranges from [] to as many as the privider schedules.
ea_appointments
id start_datetime end_datetime id_users_provider
1 '2015-09-08 14:00:00:00' '2015-09-08 14:50:00:00' 85
2 '2015-09-08 15:00:00:00' '2015-09-08 15:50:00:00' 85
3 '2015-09-17 12:00:00:00' '2015-09-17 15:50:00:00' 89
4 '2015-09-17 16:00:00:00' '2015-09-17 16:30:00:00' 89
5 '2015-09-22 11:00:00:00' '2015-09-08 11:50:00:00' 85
6 '2015-09-22 12:00:00:00' '2015-09-08 12:50:00:00' 85
7 '2015-09-22 14:00:00:00' '2015-09-08 14:50:00:00' 85
8 '2015-09-22 15:00:00:00' '2015-09-08 15:50:00:00' 85
9 '2015-09-22 17:00:00:00' '2015-09-08 17:50:00:00' 85
10 '2015-09-22 18:00:00:00' '2015-09-08 18:50:00:00' 85
I need a MySQL query that will yield the following: For provider 85 between 2015-09-07 and 2015-09-23 how many available 60 min slots exist. Result would look like this:
id_users_provider available_days available_slots
85 2015-09-08 4
85 2015-09-15 6
I see this Mysql Find free time (slots) between appointments for booking system and other similar posts. This helps me see how I can extract availability between existing appointments. I also see that I would need to join that with a table expressing the providers working plan. I am not sure how to extract the working plan into a table to join and produce the result. So, any tips will be appreciated.