How to find open appointment slots with MySQL query based on current table structure

I have three tables, one called

user_hours (where users hours are stored) - every entry represents the hours for one day only, for that user:
t1.png
user_hours_breaks (where breaks which occur during user_hours are stored -  every entry represents the break hours, if any, for one day only, for that user):
t3.png
appointments (where all appointment entries are stored):
t2.png
How do I write a MySQL query to find open time slots with 2 hour windows based upon a users hours (taking into account breaks)

Thanks..
MarkProgrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
Images are often excellent ways to help communicate, but writing DDL from images is tedious, it would be so much more helpful if you provided that so we volunteers don't have to (& obviously you already have that DDL)

Similarly it is always helpful to supply SAMPLE DATA for each table
(also in a form that we can use and not have to type)

Then, so that everyone knows precisely what you are seeking, you should provide the EXPECTED RESULT
(derived only from the sample)

-------------------
ps: It's not a matter of being lazy, it's just that we try to answer so many questions.
making it easier to answer helps you too of course (get solution faster)
MarkProgrammerAuthor Commented:
No problem, will do so in the morning

Thanks
MarkProgrammerAuthor Commented:
3 tables:
------------------------
user_hours (where users hours are stored) - every entry represents the hours for one day only, for that user:

columns:
=========
id
user
start_time
end_time

=========
rows:
=========

"1","52","2015-03-24 09:00:00","2015-03-24 19:00:00"
"1","16","2015-03-24 09:00:00","2015-03-24 19:00:00"

------------------------

user_hours_breaks (where breaks which occur during user_hours are stored -  every entry represents the break hours, if any, for one day only, for that user):

columns:
=========
id
user
start_time
end_time

=========
rows:
=========

"4","52","2015-03-24 16:00:00","2015-03-24 16:30:00"

------------------------

appointments (where all appointment entries are stored):

columns:
=========
id
user
start_time
end_time

=========
rows:
=========
"1","52","8553","2015-03-24 13:00:00","2015-03-24 15:00:00"

------------------------

Expected outcome...

function get_appointment_times_starting_now($startFromMinute='00',$appointmentDurationInMinutes,$ShowDays=1) {

      .......
      
}

so executing get_appointment_times_starting_now('00', 60,1) at 10:36AM should return:

Appointment Available With User ID 52 On 2015-03-24 From 11AM to 12PM
Appointment Available With User ID 16 On 2015-03-24 From 11AM to 12PM
Appointment Available With User ID 52 On 2015-03-24 From 12PM to 1PM
Appointment Available With User ID 16 On 2015-03-24 From 12PM to 1PM
Appointment Available With User ID 16 On 2015-03-24 From 1PM to 2PM
Appointment Available With User ID 52 On 2015-03-24 From 3PM to 4PM
Appointment Available With User ID 16 On 2015-03-24 From 2PM to 3PM
Appointment Available With User ID 16 On 2015-03-24 From 3PM to 4PM
Appointment Available With User ID 16 On 2015-03-24 From 4PM to 5PM
Appointment Available With User ID 52 On 2015-03-24 From 5PM to 6PM
Appointment Available With User ID 16 On 2015-03-24 From 5PM to 6PM
Appointment Available With User ID 52 On 2015-03-24 From 6PM to 7PM
Appointment Available With User ID 16 On 2015-03-24 From 6PM to 7PM

executing get_appointment_times_starting_now('00', 60,2) at 10:36AM should return 2 days worth of available appointments

executing get_appointment_times_starting_now('30', 60,2) at 10:36AM should return 2 days worth of available appointments but with starting with the nearest half hour, example:

Appointment Available With User ID 52 On 2015-03-24 From 11:30AM to 12:30PM

executing get_appointment_times_starting_now('00', 120,1) at 10:36AM should return appointments that can fit 2 hour intervals
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

PortletPaulEE Topic AdvisorCommented:
Here's what  I used from the above to suit the http://SQLfiddle.com "Text to DDL" button.
Note there is an unknown column in table appointments.

user_hours
id, user, start_time, end_time
1,52,2015-03-24 09:00:00,2015-03-24 19:00:00
1,16,2015-03-24 09:00:00,2015-03-24 19:00:00

user_hours_breaks
id, user, start_time, end_time
4,52,2015-03-24 16:00:00,2015-03-24 16:30:00

appointments
id,user,unknown,start_time,end_time
1,52,8553,2015-03-24 13:00:00,2015-03-24 15:00:00

It generated this (the DDL):
CREATE TABLE user_hours
    (`id` int, `user` int, `start_time` datetime, `end_time` datetime)
;
    
INSERT INTO user_hours
    (`id`, `user`, `start_time`, `end_time`)
VALUES
    (1, 52, '2015-03-24 09:00:00', '2015-03-24 19:00:00'),
    (1, 16, '2015-03-24 09:00:00', '2015-03-24 19:00:00')
;


CREATE TABLE user_hours_breaks
    (`id` int, `user` int, `start_time` datetime, `end_time` datetime)
;
    
INSERT INTO user_hours_breaks
    (`id`, `user`, `start_time`, `end_time`)
VALUES
    (4, 52, '2015-03-24 16:00:00', '2015-03-24 16:30:00')
;




CREATE TABLE appointments
    (`id` int, `user` int, `unknown` int, `start_time` datetime, `end_time` datetime)
;
    
INSERT INTO appointments
    (`id`, `user`, `unknown`, `start_time`, `end_time`)
VALUES
    (1, 52, 8553, '2015-03-24 13:00:00', '2015-03-24 15:00:00')
;

Open in new window

MarkProgrammerAuthor Commented:
Sorry, didn't understand the term DDL before but that clears it up
MarkProgrammerAuthor Commented:
The unknown column in appointments you are referring to is not necesarry for this question as it is just a reference to the client ID which the appointment is for.
PortletPaulEE Topic AdvisorCommented:
DDL = "data definition language" it is a subset of SQL

I have had some just had some bad news and may not be available for a short while.
I hope this won't be the case, but it could be.
MarkProgrammerAuthor Commented:
No problem, will be out until Thursday morning before I can test..

Thanks
PortletPaulEE Topic AdvisorCommented:
Here is a query that will generate a row for each 30 minute availability period. It will need further work.

It relies on a set of cross joins to produce 1,000 rows each with a number from 0 to 999. You might have a numbers table perhaps that you could use instead. But do note this query is limited to 1,000 currently, it's easy to expand it to larger numbers I just stopped there.

Once we have that set of numbers we need a starting date, I have used a variable @stdt for this. Taking that date we add 30 minutes * the number of the row for a "slot" starting time (and a further 30 minutes for the "slot" end time). So we generate 1000 "slots" like this which we can then compare to the user's start/end datetime values. If a "slot" fits into a users' start/end times then they are "available" for those slots. Note, this is done per user so we get a set of rows representing availability per user.

Lastly we compare this per user availability to the breaks and to appointments. For each availability slot that fits into a break or an existing appointment we remove those rows (via the two IS NULL conditions). The final rows then are when users are available for appointments.

SET @stdt := '2015-03-24';

SELECT
        avail.num
      , avail.user
      , avail.start_at   as avail_start_dt_time
      , avail.end_at     as avail_end_dt_time
      , avail.on_dt      as just_date_no_time
      , avail.start_time as user_start_time
      , avail.end_time   as user_end_time
FROM (
      SELECT
            dts.num, dts.on_dt, dts.start_at, dts.end_at, uh.user, uh.start_time, uh.end_time
      FROM (
            /* generates 1000, 30 minute "slots", starting at a variable date */
            SELECT
                  num + 1 as num
                , @stdt on_dt
                , DATE_ADD(@stdt, INTERVAL (slots.num * 30) MINUTE) start_at
                , DATE_ADD(@stdt, INTERVAL ((slots.num + 1) * 30) MINUTE) end_at
            FROM (
                  /* generates 1000 rows 0 to 999 */
                  SELECT `hundreds`.digit * 100 + `tens`.digit * 10 + `ones`.digit AS num
                  FROM (
                        SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
                        SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
                       ) `ones`
                  CROSS JOIN (
                        SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
                        SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
                             ) `tens`
                  CROSS JOIN (
                        SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
                        SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
                             ) `hundreds`
                  ) `slots`
            ) `dts`
      INNER JOIN user_hours `uh` ON uh.start_time < dts.end_at AND uh.end_time > dts.start_at
      ) `avail`
LEFT JOIN user_hours_breaks `uhb` ON  uhb.start_time < avail.end_at AND uhb.end_time > avail.start_at
LEFT JOIN appointments `app` ON  app.start_time < avail.end_at AND app.end_time > avail.start_at
WHERE uhb.id IS NULL
AND app.id IS NULL
;

Open in new window


I am not that familiar with MySQL functions/procedures so I can't really take it much further than providing you with this logic. I do hope it helps.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MarkProgrammerAuthor Commented:
Thanks! I will test it when I get back Thursday morning.
MarkProgrammerAuthor Commented:
Works PERFECT! Thanks ... could you also help in this question (it works fine through PHPMyAdmin but I have an issue running it through MySQLi)

http://www.experts-exchange.com/Database/MySQL/Q_28644685.html#a40690436
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.