Ignore overlapping/duplicated times in a timetabling Sum
-
Hello,
I have a timetabling application, out of which I need to calculate the amount of time tutors teach. Simple enough, except...
Tutors can be timetabled for Classes that clash timewise. There are business reasons for this (honest!), but I need to calculate the amount of 'unique' time. As a T-SQL example is worth a 1000 words, please see the following code, which gives an answer of 5.25 hours. But I need code that'll give me an answer of 3.5 hours!
I could of course use a cursor, but I'm trying to avoid being outed as a RBARer by Mr Moden!
CREATE TABLE #Classes (TutorID int
, Class char(1)
, StartTime smalldatetime
, EndTime smalldatetime)
INSERT INTO #Classes
SELECT 1, 'A', '19000101 09:00:00', '19000101 10:00:00'
UNION
SELECT 1, 'B', '19000101 10:00:00', '19000101 11:00:00'
UNION
SELECT 1, 'C', '19000101 10:00:00', '19000101 10:45:00'
UNION
SELECT 1, 'D', '19000101 10:30:00', '19000101 11:30:00'
UNION
SELECT 1, 'E', '19000101 14:00:00', '19000101 15:00:00'
UNION
SELECT 1, 'F', '19000101 14:15:00', '19000101 14:45:00'
SELECT TutorID
, Sum(DateDiff(minute, StartTime, EndTime))/60.00 As TotalTuition
FROM #Classes
GROUP BY TutorID
DROP TABLE #Classes
Thanks,
Barry
-
I suspect this can be simplified, but should work okay
WITH LBounds(TutorID,StartTime) AS (
SELECT s1.TutorID,
s1.StartTime
FROM #Classes s1
WHERE NOT EXISTS(SELECT * FROM #Classes s2
WHERE s1.StartTime > s2.StartTime
AND s1.StartTime <= s2.EndTime
AND s1.TutorID=s2.TutorID)),
UBounds(TutorID,EndTime) AS (
SELECT s1.TutorID,
s1.EndTime
FROM #Classes s1
WHERE NOT EXISTS(SELECT * FROM #Classes s2
WHERE s1.EndTime >= s2.StartTime
AND s1.EndTime < s2.EndTime
AND s1.TutorID=s2.TutorID)),
NoOverlaps AS (
SELECT s.TutorID,
s.StartTime,
MIN(t.EndTime) AS EndTime
FROM LBounds s
INNER JOIN UBounds t ON t.TutorID=s.TutorID AND s.StartTime <= t.EndTime
GROUP BY s.TutorID, s.StartTime)
SELECT TutorID
, Sum(DateDiff(minute, StartTime, EndTime))/60.00 As TotalTuition
FROM NoOverlaps
GROUP BY TutorID
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark, that's brilliant!! And what a quick response.
Many thanks, you are a life saver!
Barry
-
It may be worth using a number/tally table:
SELECT TutorID
,ClassDate
,COUNT(DISTINCT N.Number)/60.0 AS TotalTuition
FROM
(
SELECT TutorID
,StartTime
,EndTime
,DATEADD(d, DATEDIFF(d, 0, StartTime), 0) AS ClassDate
FROM #Classes
) D
JOIN dbo.spt_values N
ON N.Number >= DATEDIFF(minute, ClassDate, D.StartTime)
AND N.Number < DATEDIFF(minute, ClassDate, D.EndTime)
AND N.[Type] = 'P'
GROUP BY D.TutorID, D.ClassDate
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply