在TSQL中很难将事件与办公时间匹配

我确实有一个棘手的问题与时间跨度的集合。

例如

:在一家大公司，一辆汽车坏了，就交给内部的机械小组处理。我确实有时间，当它被提交和释放。我要找的是，多长时间的车在车库与人可能的工作吗?

所以我有两张表。

1)。一个带有事件开始时间和结束时间的表@e。2)。一张表@o是每个工作日的办公时间。工作日从星期一1点开始。

我需要的是额外的办公时间。

为了便于测试，我用样本数据做了一个集合:

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

我目前的方法是将所有带有eventid的事件分成带有start + end的单个天，然后计算每个事件和组在eventid上的时间，并将时间相加。这将是相当大的sql负载。如果有人有不同的方法或任何想法，我非常感谢。

为了避免这个问题:我使用MS SQL-Server 2014，我有大约1000万个事件。

谢谢你的任何帮助或想法。

前世

在我看来，像这样一个简单的查询就可以了

;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

有一个限制。您应该将alldates CTE的开始日期和结束日期定义为系统中最早和最晚的事件日期。对于这么大的数据，您应该考虑创建一个虚拟的alldates表，其中包含alldates，最好创建一个类似于CTE的完整日历表。这样，您将只需要使用最后的SELECT来获得所需的结果。

结果

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)

非常感谢CHA和其他输入。

你的答案几乎是完美的，我必须用负时间来区分几个特别的。还有一个"weekday"one_answers"id"的栏目错别字。

现在它运行得很好。

我给事件添加了一个id，这是它们最初拥有的(当然;-)。

下面是完整的工作示例代码:

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)

相关内容

最新更新

热门标签：