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(TutorIDint

    , Classchar(1)

    , StartTimesmalldatetime

    , EndTimesmalldatetime)

    INSERT INTO #Classes

    SELECT1, 'A', '19000101 09:00:00', '19000101 10:00:00'

    UNION

    SELECT1, 'B', '19000101 10:00:00', '19000101 11:00:00'

    UNION

    SELECT1, 'C', '19000101 10:00:00', '19000101 10:45:00'

    UNION

    SELECT1, 'D', '19000101 10:30:00', '19000101 11:30:00'

    UNION

    SELECT1, 'E', '19000101 14:00:00', '19000101 15:00:00'

    UNION

    SELECT1, 'F', '19000101 14:15:00', '19000101 14:45:00'

    SELECTTutorID

    , 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