使用T-SQL的日期之间的总小时数
我正在寻找一个T-Sql脚本来计算两个日期之间的总小时数，但只有当它落入特定的时期。
我有一个名为“投诉”的表，其中包含多个列，包括ContactDate和ResolvedDate。
我只想计算总时间，如果它在我们的开放时间内。星期一到星期五晚上9点到5点，星期六和星期天下午12点到4点。
例如，如果我在12月5日下午3点接到投诉，并在12月6日下午1点解决了它，那么总时数将是3小时。
另一个例子是，我在12月3日和中午12点接到投诉，并在12月6日下午5点解决了这个问题，总共是24小时。
我希望将该值放入我的报告的每一行。
回答 3
Stack Overflow用户
发布于 2016-12-08 00:20:39
是的，我不会为你做所有这些，但这会给你一个选择。我会创建一个表，其中包含每小时的一行数据。在这种情况下，我会有一个标志来显示它是否是工作时间，就像这样；
CREATE TABLE #DateTable (Time_Stamp datetime, Working_Hour int)
INSERT INTO #DateTable (Time_Stamp, Working_Hour)
VALUES
('2016-12-05 14:00:00',1)
,('2016-12-05 15:00:00',1)
,('2016-12-05 16:00:00',1)
,('2016-12-05 17:00:00',0)
,('2016-12-05 18:00:00',0)
,('2016-12-05 19:00:00',0)
,('2016-12-05 20:00:00',0)
,('2016-12-05 21:00:00',0)
,('2016-12-05 22:00:00',0)
,('2016-12-05 23:00:00',0)
,('2016-12-05 00:00:00',0)
,('2016-12-06 01:00:00',0)
,('2016-12-06 02:00:00',0)
,('2016-12-06 03:00:00',0)
,('2016-12-06 04:00:00',0)
,('2016-12-06 05:00:00',0)
,('2016-12-06 06:00:00',0)
,('2016-12-06 07:00:00',0)
,('2016-12-06 08:00:00',0)
,('2016-12-06 09:00:00',0)
,('2016-12-06 10:00:00',0)
,('2016-12-06 11:00:00',0)
,('2016-12-06 12:00:00',1)
,('2016-12-06 13:00:00',1)
,('2016-12-06 14:00:00',1)
,('2016-12-06 15:00:00',1)示例调用列表；
CREATE TABLE #Calls (Call_ID int, StartDate datetime, EndDate datetime)
INSERT INTO #Calls (Call_ID, StartDate, EndDate)
VALUES
(1,'2016-12-05 14:00:00','2016-12-06 14:00:00')
,(2,'2016-12-05 15:00:00','2016-12-06 14:00:00')
,(3,'2016-12-05 16:00:00','2016-12-06 14:00:00')这样，你就可以用你的两个日期来连接它，并将它们之间的工作时间相加，如下所示；
SELECT
c.Call_ID
,SUM(dt.Working_Hour) Working_Hour
FROM #Calls c
JOIN #DateTable dt
ON dt.Time_Stamp BETWEEN c.StartDate AND c.EndDate
GROUP BY c.Call_ID结果；
Call_ID Working_Hour
1 6
2 5
3 4如果你用谷歌搜索'date table sql‘，有很多例子可以帮助你填充日期表。
Stack Overflow用户
发布于 2016-12-08 00:35:26
从我之前做这件事的记忆中...(它不是很漂亮)
with CTE as
(
select CaseID, cast(ComplaintDate as date) as ComplaintDate, cast(CompaintDate as time) as CompTime, cast(ResolvedDate as date) as ResolvedDate, cast(ResolvedDate as time) as ResolvedTime,
case
when datepart(dw,ResolvedDate) in (1,7) then cast('12:00:00' as TIME)
else cast('09:00:00' as time)
end as ResStart,
case
when datepart(dw,CompaintDate) in (1,7) then cast('16:00:00') as time
else cast('17:00:00' as time)
end as ComEnd
from Compaints
)
select CaseID,
case
when datepart(dd, ResolvedDate) > datepart(dd,ComplaintDate) then
datediff(hh, ResolvedTime, ResStart) + datediff(hh,ComEnd,ComplaintTime) + 8*datediff(dd,ResolvedDate,ComplaintDate)
else DateDiff(hh,ResolvedTime,ComplaintTime)
end as HoursOpen
from CTEStack Overflow用户
发布于 2016-12-08 01:44:38
请考虑以下内容。
我们实际上在分钟内执行计算，因此您决定向上/向下舍入，但结果以小时为单位返回。
我应该注意到，交叉应用中的代码可以很容易地迁移到UDF中。
Declare @YourTable table (ID int,OpenDT datetime, CloseDT datetime)
Insert Into @YourTable values
(1,'2016-12-02 16:00','2016-12-05 10:00'),
(2,'2016-12-02 16:00','2016-12-05 09:45'),
(3,'2016-12-07 09:00','2016-12-08 11:15')
Select A.*
,B.BusinessHours
From @YourTable A
Cross Apply (
Select BusinessHours=count(*)/60.0
From (Select Top (DateDiff(MI,A.OpenDT,A.CloseDT)+5000) D=DateAdd(MI,Row_Number() over (Order By (Select NULL))-1,cast(cast(A.OpenDT as date) as datetime)) From master..spt_values N1,master..spt_values N2) D
Where D > A.OpenDT and D<= A.CloseDT
and (
(DatePart(DW,D) between 2 and 6 and cast(D as time) between '09:01' and '17:00')
or
(DatePart(DW,D) = 7 and cast(D as time) between '12:01' and '16:00')
)
) B返回
作为一个额外的好处，您还可以排除假日(应该是一个实际的表)
and Cast(D as Date) Not In (Select Date From (Values
('2016-01-01','New Year''s Day'),
('2016-01-18','Martin Luther King, Jr,'),
('2016-02-15','Washington''s Birthday'),
('2016-03-25','Good Friday'),
('2016-05-30','Memorial Day'),
('2016-07-04','Independence Day'),
('2016-09-05','Labor Day'),
('2016-11-24','Thanksgiving'),
('2016-11-25','Black Friday'),
('2016-12-26','Christmas Day')
) as H (Date,Name)) https://stackoverflow.com/questions/41022065
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号