Calculate open timeslots given availability and existing appointments - by day

Overview:

I have a table which stores a persons "availability" for a current day, e.g.

Mon - 8:00am - 11:30am

Mon - 1:30pm - 6:00pm

A second table stores appointments that this person already has for the same day, e.g.

Mon - 8:30am - 11:00am

Mon - 2:30pm - 4pm

Desired result:

Doing calculationsI'd like to have the following result - e.g. "this person has availability on the given day":

Mon - 8:00am - 8:30am

Mon - 11:00am - 11:30am

Mon - 1:30pm - 2:30pm

Mon - 4:00pm - 6:00pm

Any ideas on how to calculate the output given the two inputs (e.g. availability, existing appointments) would be greatly appreciated. Preferably I'd use javascript on the client to do the calculating as I would believe that doing it within the DB (I'm using MSSQL) would be slow for many records, per开发者_运维知识库sons, etc.

Hope this is enough information to illustrate the problem at hand - Many thanks in advance.

---

You need to discretize your time. Choose a time interval to use as your atom. Based on your example, that should probably be a half hour.

Now

Create table Availability (person_id int, interval_id int);
Create table Appointment (person_id int, interval_id int, appointment_desc text);

I'm leaving out the primary keys, and there should be foreign keys to lookup tables for Person and Interval.

There will be an Interval table for looking up what each interval_id stands for.

Create table Interval(interval_id int primary key, interval_start datetime, interval_end datetime)

Populate the Interval table with every interval you're going to have in your calendar. Populating it might be a chore, but you can create the actual values in Excel, then paste them into your Interval table.

Now you can find free intervals as

Select person_id, interval_id from Availability av
left join Appointment ap
on av.person_id = ap.person_id and av.interval_id = ap.interval_id
where ap.interval_id is null

MSSQL can do this kind of outer join in no time (provided you set up the keys), and you can include the list of free intervals in the pages you send, with javascript to display them when and as desired.