Tricky matching event to office hours in TSQL

Question

i do have a tricky problem with to sets of timespans.

For example: In a big company a car breaks down and is submitted to the internal mechanical team. I do have the time, when it is submitted and released. What i am looking for, how long was the car in the garage with people possible working on it?

So i do have two tables.

1.) One table @e with eventstart and endtimes. 2.) One table @o where officehours for each weekday. The weekday starts with 1 on monday.

What i need is the added amount of time passed during office hours.

I made a set with sample data, for easy testing:

declare @o table ([id] [int], [weekday] [smallint],    [starttime] [time](7), [endtime] [time](7))

insert into @o([id], [weekday], [starttime], [endtime]) values
(1, 1, CAST(N'09:00:00' AS Time), CAST(N'17:00:00' AS Time)),
(2, 2, CAST(N'09:00:00' AS Time), CAST(N'17:00:00' AS Time)),
(3, 3, CAST(N'09:00:00' AS Time), CAST(N'17:00:00' AS Time)),
(4, 4, CAST(N'09:00:00' AS Time), CAST(N'17:00:00' AS Time)),
(5, 5, CAST(N'09:00:00' AS Time), CAST(N'14:00:00' AS Time)),
(6, 6, CAST(N'12:00:00' AS Time), CAST(N'15:00:00' AS Time)),
(7, 7, CAST(N'12:00:00' AS Time), CAST(N'15:00:00' AS Time))

declare @e table (    [startspan] [datetime],    [endspan] [datetime] )

INSERT INTO @e ([startspan], [endspan]) VALUES 
(CAST(N'2015-05-06 15:08:59' AS DateTime), CAST(N'2015-05-13 09:52:09' AS DateTime)),
(CAST(N'2015-05-05 20:46:17' AS DateTime), CAST(N'2015-05-08 11:34:50' AS DateTime)),
(CAST(N'2015-05-02 14:42:23' AS DateTime), CAST(N'2015-05-05 17:22:30' AS DateTime)),
(CAST(N'2015-05-01 09:07:36' AS DateTime), CAST(N'2015-05-04 08:31:35' AS DateTime)),
(CAST(N'2015-05-01 00:16:00' AS DateTime), CAST(N'2015-05-04 12:58:27' AS DateTime)),
(CAST(N'2015-04-30 19:14:25' AS DateTime), CAST(N'2015-05-05 20:29:48' AS DateTime)),
(CAST(N'2015-04-24 12:48:34' AS DateTime), CAST(N'2015-04-27 16:15:22' AS DateTime)),
(CAST(N'2015-04-22 13:05:29' AS DateTime), CAST(N'2015-04-27 11:13:28' AS DateTime)),
(CAST(N'2015-04-18 11:01:17' AS DateTime), CAST(N'2015-04-20 15:44:41' AS DateTime)),
(CAST(N'2015-04-18 09:49:51' AS DateTime), CAST(N'2015-04-20 12:18:42' AS DateTime))

My current approach would be to split all events with an eventid into single days with start + end and then calculate the time for each event and group over eventid and sum the time. That would be quite a load of sql. If anyone has a different approach or any ideas it is very much appreciated.

To avoid the question: I use MS SQL-Server 2014, i have about 10 Million events.

Thanks for any help or ideas you might have.

Metanormal

Answer 1

In my opinion a simple query like this will do the trick

;with alldates as 
(
select convert(datetime, '2015-04-01') as d
union all select DATEADD(day, 1, d) from alldates where d < '2015-06-01'
),
oh as 
(
select alldates.d
, alldates.d + CONVERT(datetime, #o.starttime) as starttime
, alldates.d + CONVERT(datetime, #o.endtime) as endtime
,datediff(minute, alldates.d + CONVERT(datetime, #o.starttime), alldates.d + CONVERT(datetime, #o.endtime)) as tothours
from alldates inner join #o on DATEPART(weekday, alldates.d) = #o.id
)
select #e.[startspan], #e.[endspan], 
SUM(
datediff(minute, 
            case when cast(#e.[startspan] as date)=oh.d then #e.[startspan] else oh.starttime end,
            case when cast(#e.[endspan] as date)=oh.d then #e.[endspan] else oh.endtime end) 
            ) as totminutes
from oh
inner join #e on cast(#e.[startspan] as date) <= oh.d and cast(#e.[endspan] as date) >= oh.d
--where #e.startspan = '2015-05-06 15:08:59'
group by #e.[startspan], #e.[endspan]
order by 1

There is a limitation. You should define the start and end dates for the alldates CTE as the earliest and latest event dates in your system. For the data that big you should consider creating a dummy alldates table with alldates in it, and even better, create the whole calendar table similar to the oh CTE. That way you will need to use just the final SELECT to get the desired result.

Results

startspan               endspan                 totminutes
----------------------- ----------------------- -----------
2015-04-18 09:49:51.000 2015-04-20 12:18:42.000 989
2015-04-18 11:01:17.000 2015-04-20 15:44:41.000 1123
2015-04-22 13:05:29.000 2015-04-27 11:13:28.000 1508
2015-04-24 12:48:34.000 2015-04-27 16:15:22.000 1227
2015-04-30 19:14:25.000 2015-05-05 20:29:48.000 1695
2015-05-01 00:16:00.000 2015-05-04 12:58:27.000 1782
2015-05-01 09:07:36.000 2015-05-04 08:31:35.000 984
2015-05-02 14:42:23.000 2015-05-05 17:22:30.000 1480
2015-05-05 20:46:17.000 2015-05-08 11:34:50.000 528
2015-05-06 15:08:59.000 2015-05-13 09:52:09.000 2264

(10 row(s) affected)

Answer 2

Many thanx to CHA and the other input.

The answer from cha was almost perfect, i had to case out a few specials with negative time. and there was a column-typo with weekday] and id.

And now it runs perfectly.

I added an id to the events, which they originally have (of course ;-).

Here is the complete working examplecode:

declare @o table ([oid] [int], [weekday] [smallint],    [starttime] [time](7), [endtime] [time](7))

insert into @o([oid], [weekday], [starttime], [endtime]) values
(1, 1, CAST(N'09:00:00' AS Time), CAST(N'17:00:00' AS Time)),
(2, 2, CAST(N'09:00:00' AS Time), CAST(N'17:00:00' AS Time)),
(3, 3, CAST(N'09:00:00' AS Time), CAST(N'17:00:00' AS Time)),
(4, 4, CAST(N'09:00:00' AS Time), CAST(N'17:00:00' AS Time)),
(5, 5, CAST(N'09:00:00' AS Time), CAST(N'14:00:00' AS Time)),
(6, 6, CAST(N'12:00:00' AS Time), CAST(N'15:00:00' AS Time)),
(7, 7, CAST(N'12:00:00' AS Time), CAST(N'15:00:00' AS Time))

declare @e table ( [eid] [int],   [startspan] [datetime],    [endspan] [datetime] )

INSERT INTO @e ([eid], [startspan], [endspan]) VALUES 
(1,CAST(N'2015-05-06 15:08:59' AS DateTime), CAST(N'2015-05-13 09:52:09' AS DateTime)),
(2,CAST(N'2015-05-05 20:46:17' AS DateTime), CAST(N'2015-05-08 11:34:50' AS DateTime)),
(3,CAST(N'2015-05-02 14:42:23' AS DateTime), CAST(N'2015-05-05 17:22:30' AS DateTime)),
(4,CAST(N'2015-05-01 09:07:36' AS DateTime), CAST(N'2015-05-04 08:31:35' AS DateTime)),
(5,CAST(N'2015-05-01 00:16:00' AS DateTime), CAST(N'2015-05-04 12:58:27' AS DateTime)),
(6,CAST(N'2015-04-30 19:14:25' AS DateTime), CAST(N'2015-05-05 20:29:48' AS DateTime)),
(7,CAST(N'2015-04-24 12:48:34' AS DateTime), CAST(N'2015-04-27 16:15:22' AS DateTime)),
(8,CAST(N'2015-04-22 13:05:29' AS DateTime), CAST(N'2015-04-27 11:13:28' AS DateTime)),
(9,CAST(N'2015-04-18 11:01:17' AS DateTime), CAST(N'2015-04-20 15:44:41' AS DateTime)),
(10,CAST(N'2015-04-18 09:49:51' AS DateTime), CAST(N'2015-04-20 12:18:42' AS DateTime))


set datefirst 1
;with alldates as 
(
select convert(datetime, '2015-01-01') as d
union all select DATEADD(day, 1, d) from alldates where d < '2015-07-01'
) , oh
 as 
(
select alldates.d
, alldates.d + CONVERT(datetime, o.[starttime]) as starttime
, alldates.d + CONVERT(datetime, o.endtime) as endtime
,datediff(minute, alldates.d + CONVERT(datetime, o.starttime), alldates.d + CONVERT(datetime, o.endtime)) as tothours
from alldates inner join @o as o on DATEPART(weekday, alldates.d) = o.[weekday]
)
select e.eid, e.[startspan], e.[endspan]
,sum(
datediff(minute, 
            case when cast(e.[startspan] as date)=oh.d 
                then 
                    case when e.[startspan] < starttime
                        then starttime
                        else 
                            case when e.[startspan] < endtime
                                then e.[startspan]
                                else endtime
                            end
                    end
                else 
                    oh.starttime 
                end,
            case when cast(e.[endspan] as date)=oh.d 
                then 
                    case when e.[endspan] < endtime
                        then 
                            case when e.[endspan] < starttime
                                then starttime
                                else e.[endspan]
                            end
                        else
                         e.[endspan]
                    end
                else 
                    oh.endtime 
                end) ) totminutes
from oh
inner join @e as e on cast(e.[startspan] as date) <= oh.d and cast(e.[endspan] as date) >= oh.d
--where e.startspan = '2015-05-01 09:07:36.000'
group by e.[eid], e.[startspan], e.[endspan]
order by e.[eid]
option (maxrecursion 0)