MySQL schedule conflicts
Hey I came across this site looking for solutions to overlap events in mySQL tables. I was so impressed with the solution (which already helps). I thought I'd see if I could get more help ...
Okay, so Joe wants to swap places with someone at work. He has a court date. He goes to the exchange shift form and he pulls the schedule for this week (or what's left of him). This is done using a database query. No sweat. He chooses a shift. From that moment on, it becomes prickly.
So first, the form passes the start of the shift and the shift to the end of the script. It does a query for everyone who has a shift that overrides that shift. They cannot work two shifts at once, so all user IDs from this request are blacklisted. This request looks like this:
SELECT DISTINCT user_id FROM shifts
WHERE
FROM_UNIXTIME('$swap_shift_start') < shiftend
AND FROM_UNIXTIME('$swap_shift_end') > shiftstart
We then run a query for all shifts that are a) the same length (company policy), and b) do not overlap with other changes that Joe is working on.
I currently have something like this:
SELECT *
FROM shifts
AND shiftstart BETWEEN FROM_UNIXTIME('$startday') AND FROM_UNIXTIME('$endday')
AND user_id NOT IN ($busy_users)
AND (TIME_TO_SEC(TIMEDIFF(shiftend,shiftstart)) = '$swap_shift_length')
$conflict_dates
ORDER BY shiftstart, lastname
Now you are probably wondering, "What is $ conflict_dates?"
Well, when Joe sends a swap shift, he reloads his shifts for a week if he decides to test a different shift potential. So when it makes that first request and the script loops through and prints out its options, it also builds a line that looks something like this:
AND NOT(
'joe_shift1_start' < shiftend
AND 'joe_shift1_end' > shiftstart)
AND NOT(
'joe_shift2_start' < shiftend
AND 'joe_shift2_end' > shiftstart)
...etc
To make the database receive a rather long query line by line:
SELECT *
FROM shifts
AND shiftstart BETWEEN FROM_UNIXTIME('$startday') AND FROM_UNIXTIME('$endday')
AND user_id NOT IN ('blacklisteduser1', 'blacklisteduser2',...etc)
AND (TIME_TO_SEC(TIMEDIFF(shiftend,shiftstart)) = '$swap_shift_length')
AND NOT(
'joe_shift1_start' < shiftend
AND 'joe_shift1_end' > shiftstart)
AND NOT(
'joe_shift2_start' < shiftend
AND 'joe_shift2_end' > shiftstart)
AND NOT(
'joe_shift3_start' < shiftend
AND 'joe_shift3_end' > shiftstart)
AND NOT(
'joe_shift4_start' < shiftend
AND 'joe_shift4_end' > shiftstart)
...etc
ORDER BY shiftstart, lastname
So, my hope is that either SQL has some ingenious way of handling this in an easier way, or that someone can point out a fantastic logic diagram that explains potential conflicts in a much smarter way. (Note the use of "start> end, end <start" before I found that I was using betweens and had to subtract the minute from both ends.)
Thank!
AND
source to share
I think you should be able to exclude Joe's other shifts by using an inner select instead of the generated string, something like:
SELECT *
FROM shifts s1
AND shiftstart BETWEEN FROM_UNIXTIME('$startday') AND FROM_UNIXTIME('$endday')
AND user_id NOT IN ($busy_users)
AND (TIME_TO_SEC(TIMEDIFF(shiftend,shiftstart)) = '$swap_shift_length')
AND (SELECT COUNT(1) FROM shifts s2
WHERE s2.user_id = $joes_user_id
AND s1.shiftstart < s2.shiftend
AND s2.shiftstart < s1.shiftend) = 0
ORDER BY shiftstart, lastname
Basically, each row has an internal query to count Joe's offsets that overlap and make sure it is zero. This way, only rows are returned that do not intersect with any of Joe's existing offsets.
source to share
You can load the values joe_shift{1,2,3}
into the TEMPORARY table and then run a query to join it using an outer join to find only the shift that doesn't match:
CREATE TEMPORARY TABLE joes_shifts (
shiftstart DATETIME
shiftend DATETIME
);
INSERT INTO joes_shifts (shiftstart, shiftend) VALUES
('$joe_shift1_start', '$joe_shift1_end'),
('$joe_shift2_start', '$joe_shift2_end'),
('$joe_shift3_start', '$joe_shift3_end'),
('$joe_shift4_start', '$joe_shift4_end');
-- make sure you have validated these variables to prevent SQL injection
SELECT s.*
FROM shifts s
LEFT OUTER JOIN joes_shifts j
ON (j.shiftstart < s.shiftend OR j.shiftend > s.shiftstart)
WHERE j.shiftstart IS NULL
AND s.shiftstart BETWEEN FROM_UNIXTIME('$startday') AND FROM_UNIXTIME('$endday')
AND s.user_id NOT IN ('blacklisteduser1', 'blacklisteduser2',...etc)
AND (TIME_TO_SEC(TIMEDIFF(s.shiftend,s.shiftstart)) = '$swap_shift_length');
Because of the LEFT OUTER JOIN, when there is joes_shifts
no matching row, the columns are NULL.
source to share