Overlaping time intervals calculations

[Rombolt]

Hi I have a MSSQL table with many time intervals stored as datetime. Each time interval is also assigned a numeric type that specifies what type of job was done during the time interval.

I need to add-up the time intervals by type, make sure overlaping portions of intervals are not accounted for twice and also respect interval type priorities. Here's a theoric example:

Priority is given to types 30, 50 and 70. Meaning that when there's an overlap between one of these types and another one (40,60) the overlaping portion is given to the type with priority and removed from the type without priority. If there is an overlap between to types with priority, the time is kept for both.

Example 1:

Type, Start, End

30, 2006-11-01 09:00:00, 2006-11-01 11:00:00
30, 2006-11-01 13:00:00, 2006-11-01 17:00:00
40, 2006-11-01 16:30:00, 2006-11-01 17:30:00
50, 2006-11-01 17:30:00, 2006-11-01 18:30:00

Result:
Type, interval (h)

30,6.0
40,0.5
50,1.0

Example 2:

Type, Start, End

30, 2006-11-01 09:00:00, 2006-11-01 11:00:00
30, 2006-11-01 13:00:00, 2006-11-01 17:00:00
40, 2006-11-01 16:30:00, 2006-11-01 17:30:00
50, 2006-11-01 16:30:00, 2006-11-01 18:30:00

Result:
Type, interval (h)

30,6.0
40,0.0
50,2.0


My plan is to go with a stored procedure an cursors but if I could avoid it I would appreciate it. This query will be attaches to a Crystal Report report to produce a graph. of the hours spent by work type. Start and End datetime parameters would be keyed uppon printing the report and the data would be dynamically calculated.

Thanks a lot

_______________ _______________ ______
Rombolt

[almaz]

OK, here you go. For most of the simple cases when overlapping is a pretty rare case it will work, but it produces incorrect results for certain cases described in the code, and it cannot be fixed with simple queries (actually I'm not sure if it can be fixed in T-SQL without thousands-of-lines SP). I tried to describe the bug in comments, hope you'll understand.

declare @tasks table (id int, ispriority bit)
insert @tasks values (30, 1)
insert @tasks values (40, 0)
insert @tasks values (50, 1)
insert @tasks values (60, 0)
insert @tasks values (70, 1)
 
declare @schedule table(taskid int, startdate datetime, enddate datetime)
--First example:
--insert @schedule values (30, '2006-11-01 09:00:00', '2006-11-01 11:00:00')
--insert @schedule values (30, '2006-11-01 13:00:00', '2006-11-01 17:00:00')
--insert @schedule values (40, '2006-11-01 16:30:00', '2006-11-01 17:30:00')
--insert @schedule values (50, '2006-11-01 17:30:00', '2006-11-01 18:30:00')
 
--Second example:
--insert @schedule values (30, '2006-11-01 09:00:00', '2006-11-01 11:00:00')
--insert @schedule values (30, '2006-11-01 13:00:00', '2006-11-01 17:00:00')
--insert @schedule values (40, '2006-11-01 16:30:00', '2006-11-01 17:30:00')
--insert @schedule values (50, '2006-11-01 16:30:00', '2006-11-01 18:30:00')
 
 
-- contains schedule records with additional overlapping higher-priority records
-- note: it will incorrectly parse situations when several non-overlapping between each other higher priority
-- schedule records will overlap a single schedule row. Example:
 
-- high-priority rows will be treated as:
--                         [-----------------------------]
-- actual higher priority: [---]    [---]  [-------------]
-- standard priority:   [------------------------------]
declare @parsedschedule table (
    taskid int, startdate datetime, enddate datetime, overlapped_startdate datetime, overlapped_enddate datetime)
 
insert @parsedschedule
select main.taskid, main.startdate, main.enddate, min(overlapping_schedule.startdate), max(overlapping_schedule.enddate)
from @schedule main 
    inner join @tasks tasks on main.taskid = tasks.id
    left join 
    (
        select startdate, enddate 
        from @schedule 
        where taskid in (select id from @tasks where ispriority = 1)
    ) overlapping_schedule on
        -- this check ensures that current schedule row doesn't belong to high-priority tasks
        tasks.ispriority = 0 and
        -- this check filters out only those schedules overlapping current main schedule row
        main.startdate < overlapping_schedule.enddate and main.enddate > overlapping_schedule.startdate
group by main.taskid, main.startdate, main.enddate
 
-- select * from @parsedschedule
 
select taskid, 24 * sum(
    convert(float, 
        enddate - startdate - isnull(
            case when enddate<overlapped_enddate then enddate else overlapped_enddate end -
            case when startdate>overlapped_startdate then startdate else overlapped_startdate end, 0)))
from @parsedschedule
group by taskid

[Rombolt]

Thank you Almaz, I will give it a try and let you know.

I'm not certain I understand which case can create a bug but I guess I will see it when trying with more examples.

Thanks again.

Rombolt