根据工作班次计算日期时间 [英] Date Time Calculation According to Workshift
本文介绍了根据工作班次计算日期时间的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我创建了以下查询并封闭了它的输出。现在我希望
ITSU 和网络将根据工作日(即周日)的工作时间计算 到星期四7:00到15:00)。
WITH cteData
AS(
SELECT
Request_ID,
DATEDIFF(第二,MIN(状态= 400 THEN,Status_Date ELSE NULL END),MAX(状态= 800 THEN,Status_Date ELSE NULL END))AS ITSU,
DATEDIFF(第二,MIN(状态= 508,然后是Status_Date ELSE NULL END),MAX(状态= 990 THEN,状态_时间为空,结束时))AS网络
FROM usm_request_status
GROUP BY
Request_ID
)
SELECT Request_ID,
Convert(varchar(5),DateDiff(day,'1900-01-01',ITSU / 86400))+' day'+ Format(dateadd(second,ITSU,'1970-01-01'), 'H \ hour mm \ minu\te') 作为ITSU,
转换(varchar(5),DateDiff(日,'1900-01-01',网络/ 86400))+'日'+格式(dateadd(第二,网络,'1970) -01-01'), 'H \ hour mm \ minu\te') as network $
FROM cteData;
解决方案
所需要的输出
Hi,
I have created below query and enclosed its output. Now I want that ITSU and Network will be calculated based on business hours during working days (i.e Sunday to Thursday 7:00 to 15:00).
WITH cteDataAS (
SELECT Request_ID,
DATEDIFF(SECOND, MIN(CASE WHEN Status = 400 THEN Status_Date ELSE NULL END), MAX(CASE WHEN Status = 800 THEN Status_Date ELSE NULL END)) AS ITSU,
DATEDIFF(SECOND, MIN(CASE WHEN Status = 508 THEN Status_Date ELSE NULL END), MAX(CASE WHEN Status = 990 THEN Status_Date ELSE NULL END)) AS Network
FROM usm_request_status
GROUP BY Request_ID
)
SELECT Request_ID,
Convert(varchar(5),DateDiff(day,'1900-01-01',ITSU/86400)) + ' day '+Format(dateadd(second, ITSU,'1970-01-01'), 'H \hour mm \minu\te') as ITSU,
Convert(varchar(5),DateDiff(day,'1900-01-01',Network/86400)) + ' day '+Format(dateadd(second, Network,'1970-01-01'), 'H \hour mm \minu\te') as Network
FROM cteData;
解决方案
What is desired output?
这篇关于根据工作班次计算日期时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文