StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL schedule conflicts
text
Body
copied!<p>Hey, I stumbled upon this site looking for solutions for event overlaps in mySQL tables. I was SO impressed with the solution (which is helping already) I thought I'd see if I could get some more help...</p> <p>Okay, so Joe want's to swap shifts with someone at work. He has a court date. He goes to the shift swap form and it pull up this week's schedule (or what's left of it). This is done with a DB query. No sweat. He picks a shift. From this point, it gets prickly.</p> <p>So, first, the form passes the shift start and shift end to the script. It runs a query for anyone who has a shift that overlaps this shift. They can't work two shifts at once, so all user IDs from this query are put on a black list. This query looks like:</p> <pre><code>SELECT DISTINCT user_id FROM shifts WHERE FROM_UNIXTIME('$swap_shift_start') < shiftend AND FROM_UNIXTIME('$swap_shift_end') > shiftstart </code></pre> <p>Next, we run a query for all shifts that are a) the same length (company policy), and b) don't overlap with any other shifts Joe is working.</p> <p>What I currently have is something like this:</p> <pre><code>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 </code></pre> <p>Now, you are probably wondering "what is $conflict_dates???"</p> <p>Well, when Joe submits the swap shift, it reloads his shifts for the week in case he decides to check out another shift's potential. So when it does that first query, while the script is looping through and outputting his choices, it is also building a string that looks kind of like:</p> <pre><code>AND NOT( 'joe_shift1_start' < shiftend AND 'joe_shift1_end' > shiftstart) AND NOT( 'joe_shift2_start' < shiftend AND 'joe_shift2_end' > shiftstart) ...etc </code></pre> <p>So that the database is getting a pretty long query along the lines of:</p> <pre><code>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 </code></pre> <p>So, my hope is that either SQL has some genius way of dealing with this in a simpler way, or that someone can point out a fantastic logical principal that accounts for the potential conflicts in a much smarter way. (Notice the use of the 'start > end, end < start', before I found that I was using betweens and had to subtract a minute off both ends.)</p> <p>Thanks!</p> <p>A</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload