How to make calculation on time intervals?

Question

I have a problem ,i solve it but i have written a long procedure and i can't be sure that it covers all the possible cases .

The problem:

If i have a main interval time (From A to B), and secondary interval times (Many or no)

(`From X to Y AND From X` to Y` AND X`` to  Y`` AND ....`) 

I want to SUM all parts of My Main interval time (AB) out of secondary intervals in minutes in efficient and the least number of conditions (SQL server Procedure and C# method)?

For Example : If my Main interval From 02:00 to 10:30 And say one secondary interval From 04:00 to 08:00

Now i want this result : ((04:00 - 02:00) + (10:30 -08:00))* 60

Example with graph :

in the first case the result will be :

((X-A) + (B-Y)) * 60

and it will be more complicated when i have many secondary periods.

NOTE:

May be the overlap among the secondary intervals happening only when i have to compare the main period [A,B] to the UNION of at most two parallel sets of secondary intervals .the first set have to contain only one secondary interval and the the second set contains (many or no ) of secondary intervals .For example in the graph comparing [A,B] to (sets of 2,5)the first set (2) consists of one secondary interval and the second set (5) consists of three secondary intervals . and this 's the most worst case ,i need to handle.

For example :

IF my main interval is [15:00,19:40] and i have two sets of secondary intervals .according to my rule at least one of these sets should consists of one secondary interval. say the first set is [11:00 ,16:00] and the second set is consists of say two secondary intervals [10:00,15:00],[16:30,17:45] Now i want the result (16:30 -16:00) +(19:40 -17:45)

---

According to the comments :

My table is like this :

The first table contains secondary periods ,at most two sets of secondary periods in the same date for specific employee. the first set contains only one secondary period in the work day (W) [work_st,work_end],and this set will be empty if the day is weekend [E] and in this case no overlap among the secondary periods. and the second set may contain many secondary periods in the same date [check_in,check_out] ,because the employee may check_in_out many times in the same day.

emp_num  day_date   work_st    work_end   check_in   check_out     day_state

  547    2015-4-1   08:00       16:00     07:45      12:10           W
  547    2015-4-1   08:00       16:00     12:45      17:24           W
  547    2015-4-2   00:00       00:00     07:11      13:11           E

The second table contains the main period[A,B] and it's a one period for this employee at that day (one record)

emp_num  day_date   mission_in    mission_out
  547    2015-4-1    15:00          21:30
  547    2015-4-2    8:00           14:00

---

In the previous example if i have a procedure or method as required this procedure should take two parameters :

• The Date
• The emp_num

in the previous example it should be like this ('2015-4-1' ,547)

According to my explanation :

• The Main period (Mission Period) [A,B] from the second table : Should be only one period in this date for that employee

  [15:00,21:30]

• The secondary period for the passed date ('2015-4-1') for that employee was two sets of secondary periods (the worst case) from the first table

  The first set should contain only one secondary period (or zero periods) [08:00,16:00] the second set could contain many secondary periods (or zero periods)

  [07:45,12:10],[12:45,17:24]

The output should be [17:24,21:30] converted to minutes

Note

all day_date,mission_in,mission_out,work_st,work_end,check_in,check_out are datetime fields but i put just the time in the example for simplification , i want to ignore the date part except the day_date because it's the date which i calculate based on in addition to the emp_num.

Answer 1

I had to solve this problem to digest some scheduling data. This allows multiple online times, but assumes that they do not overlap.

select convert(datetime,'1/1/2015 5:00 AM') StartDateTime,  convert(datetime,'1/1/2015 5:00 PM') EndDateTime, convert(varchar(20),'Online') IntervalType into #CapacityIntervals
insert into #CapacityIntervals select '1/1/2015 4:00 AM' StartDateTime,  '1/1/2015 6:00 AM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 5:00 AM' StartDateTime,  '1/1/2015 6:00 AM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 10:00 AM' StartDateTime,  '1/1/2015 12:00 PM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 11:00 AM' StartDateTime,  '1/1/2015 1:00 PM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 4:00 PM' StartDateTime,  '1/1/2015 6:00 PM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 1:30 PM' StartDateTime,  '1/1/2015 2:00 PM' EndDateTime, 'Offline' IntervalType

    --Populate your Offline table
    select 
        ROW_NUMBER() over (Order by StartDateTime, EndDateTime) Rownum,
        StartDateTime, 
        EndDateTime
    into #Offline
    from #CapacityIntervals
    where IntervalType in ('Offline','Cleanout')
    group by StartDateTime, EndDateTime

    --Populate your Online table
    select 
        ROW_NUMBER() over (Order by StartDateTime, EndDateTime) Rownum,
        StartDateTime, 
        EndDateTime
    into #Online
    from #CapacityIntervals
    where IntervalType not in ('Offline','Cleanout')


    --If you have overlapping online intervals... check for those here and consolidate.


    -------------------------------
    --find overlaping offline times
    -------------------------------
    declare @Finished as tinyint
    set @Finished = 0

    while @Finished = 0
    Begin
        update #Offline
        set #Offline.EndDateTime = OverlapEndDates.EndDateTime
        from #Offline
        join
            (
            select #Offline.Rownum,
                MAX(Overlap.EndDateTime) EndDateTime
            from #Offline
            join #Offline Overlap
            on Overlap.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
                and #Offline.Rownum <= Overlap.Rownum
            group by #Offline.Rownum
            ) OverlapEndDates
        on #Offline.Rownum = OverlapEndDates.Rownum

        --Remove Online times completely inside of online times
        delete #Offline
        from #Offline
        join #Offline Overlap
        on #Offline.StartDateTime between Overlap.StartDateTime and Overlap.EndDateTime
            and #Offline.EndDateTime between Overlap.StartDateTime and Overlap.EndDateTime
            and #Offline.Rownum > Overlap.Rownum

        --LOOK IF THERE ARE ANY MORE CHAINS LEFT    
        IF NOT EXISTS(
                select #Offline.Rownum,
                    MAX(Overlap.EndDateTime) EndDateTime
                from #Offline
                join #Offline Overlap
                on  Overlap.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
                    and #Offline.Rownum < Overlap.Rownum
                group by #Offline.Rownum
                )
            SET @Finished = 1
    END

    -------------------------------
    --Modify Online times with offline ranges
    -------------------------------

    --delete any Online times completely inside offline range
    delete #Online 
    from #Online
    join #Offline
    on #Online.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
        and #Online.EndDateTime between #Offline.StartDateTime and #Offline.EndDateTime

    --Find Online Times with offline range at the beginning
    update #Online
    set #Online.StartDateTime = #Offline.EndDateTime
    from #Online
    join #Offline
    on #Online.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
        and #Online.EndDateTime >= #Offline.EndDateTime

    --Find Online Times with offline range at the end
    update #Online
    set #Online.EndDateTime = #Offline.StartDateTime
    from #Online
    join #Offline
    on #Online.StartDateTime <= #Offline.StartDateTime
        and #Online.EndDateTime between #Offline.StartDateTime and #Offline.EndDateTime

    --Find Online Times with offline range punched in the middle
    select #Online.Rownum, 
        #Offline.Rownum OfflineRow,
        #Offline.StartDateTime,
        #Offline.EndDateTime,
        ROW_NUMBER() over (Partition by #Online.Rownum order by #Offline.Rownum Desc) OfflineHoleNumber
    into #OfflineHoles
    from #Online
    join #Offline
    on #Offline.StartDateTime between #Online.StartDateTime and #Online.EndDateTime
        and #Offline.EndDateTime between #Online.StartDateTime and #Online.EndDateTime

    declare @HoleNumber as integer
    select @HoleNumber = isnull(MAX(OfflineHoleNumber),0) from #OfflineHoles

    --Punch the holes out of the online times
    While @HoleNumber > 0
    Begin
        insert into #Online 
        select
            -1 Rownum,
            #OfflineHoles.EndDateTime StartDateTime,
            #Online.EndDateTime EndDateTime
        from #Online
        join #OfflineHoles
        on #Online.Rownum = #OfflineHoles.Rownum
        where OfflineHoleNumber = @HoleNumber

        update #Online
        set #Online.EndDateTime = #OfflineHoles.StartDateTime
        from #Online
        join #OfflineHoles
        on #Online.Rownum = #OfflineHoles.Rownum
        where OfflineHoleNumber = @HoleNumber

        set @HoleNumber=@HoleNumber-1
    end

--Output total hours
select SUM(datediff(second,StartDateTime, EndDateTime)) / 3600.0 TotalHr
from #Online

--see how it split up the online intervals
select * 
from #Online
order by StartDateTime, EndDateTime