Calculationg with intertwines timespans

Transact-SQL
1

Hi,
I have two tables on with eventstart and endtimes.

And another table where officehours are given.

I need to look the events up and get the amount of time an event happened inside the officehours.

I will post a SQL with sample data to use for experiments.

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 possible people working on it?

The weekday starts with 1 on monday. Thanks for any help or ideas you might have.

TheCleaningWoman

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))
2

Hi, ich habe die Frage auch bei stackoverflow gepostet. Und so steht dort nun auch die Lösung.
Danke, fürs lesen.

Sorry, der Editor erlaubt mir das posten nicht.

Link: stackoverflow Question ID = 30772062
Title: tricky-matching-event-to-office-hours-in-tsql

Oder direkt als ausführbarer Code:

3

;with cte
as
(
select *, startspan as workeddays, case when datepart(DW, startspan)=1 then 7 else datepart(DW, startspan)-1 end as d from @e
union all
select startspan, endspan , workeddays +1, case when datepart(DW, workeddays+1)=1 then 7 else datepart(DW, workeddays+1)-1 end as d
from cte
where endspan >= workeddays + 1
)

select a.startspan, a.endspan, count(a.workeddays) workeddays, sum(datediff(HH,b.starttime , b.endtime)) workedhours
from cte a
inner join @o b
on a.d = b.id
group by a.startspan, a.endspan