Ignore overlapping/duplicated times in a timetabling Sum
-
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 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply