StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Tested it now: <a href="http://sqlfiddle.com/#!3/a4e7a/2" rel="nofollow">http://sqlfiddle.com/#!3/a4e7a/2</a></p> <p>I've assumed a user table. There's lots of scope for performance improvement. E.g by adding a where clause that eliminates ranges that don't overlap. It'll probably also be faster if you replace the function with a hideous case statement.</p> <p>The tricky bit is figuring out the algorithm for how much of two timespans overlap. I always find it useful to draw pictures of the cases:</p> <pre><code>Case 1 |------| |=======| Case 2 |------| |======| Case 3 |-------| |===| </code></pre> <p>and the equivalent with the orders reversed.</p> <p>It turns out the overlap is the minimum of the two end times minus the maximum of the two start times. (If negative there is no overlap). I always have to check all the cases to re-convince myself of this.</p> <pre><code>-- Function that determines how many minutes of overlap there are between two timespans Create Function dbo.MinutesOverlap( @Start1 as datetime, @End1 as datetime, @Start2 as datetime, @End2 as datetime ) Returns int As Begin Declare @MaxStart As datetime, @MinEnd As datetime, @Ret int = 0 Set @MaxStart = Case When @Start1 > @Start2 Then @Start1 Else @Start2 End Set @MinEnd = Case When @End1 > @End2 Then @End2 Else @End1 End If @MaxStart < @MinEnd Set @Ret = DateDiff(Minute, @MaxStart, @MinEnd) Return @Ret End Select u.UserID, e.EventID, Sum(dbo.MinutesOverlap(e.eventStart, e.eventEnd, a.availStart, a.availEnd)) From Event e Cross Join User u Left Outer Join Available a On u.UserID = a.UserID Group By u.UserID, e.EventID </code></pre>

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