将SQL中所有代理的移位时间总计累计小时数，分钟数，秒数

Question

我需要计算所有代理程序的总登录次数。

+-----------------------+-------------+-----------------------+-----------------------+ 
|ShiftDateAndTime  |EmployeeID |LoginTime    |LogoutTime    | 
+-----------------------+-------------+-----------------------+-----------------------+ 
|2017-03-07 08:59:56.000| 81200  |2017-03-07 08:59:56.000|2017-03-07 17:30:43.000| 
+-----------------------+-------------+-----------------------+-----------------------+ 
|2017-03-07 07:35:12.000| 82300  |2017-03-07 07:35:12.000|2017-03-07 16:04:30.000| 
+-----------------------+-------------+-----------------------+-----------------------+ 
|2017-03-07 09:38:58.000| 81800  |2017-03-07 09:38:58.000|2017-03-07 18:02:59.000| 
+-----------------------+-------------+-----------------------+-----------------------+ 
|2017-03-07 09:18:52.000| 83240  |2017-03-07 09:18:52.000|2017-03-07 18:08:54.000| 
+-----------------------+-------------+-----------------------+-----------------------+ 

我想计算2017-03-07跨所有业务代表的总累计工时。例如，

Sum(LogoutTime - LoginTime for Agent1, Agent2, Agent3 and Agent4.) 

我需要时间在hh:mm:ss。我正在使用SQL Server 2014来运行查询。

我曾尝试：

选择
（SUM（DATEDIFF（第二，LoginTime，LogoutTime））/ 3600）[TOTALTIME]
FROM AgentStats

但我只得到时间在几分钟内。我需要它在hh：mm：ss。

Answer 1

我已经使用了CTE只是为了可以很容易地格式化结果为HH：MM：SS

WITH acm AS 
(
    SELECT SUM (DATEDIFF(second, LoginTime, LogoutTime)) [Seconds] 
    FROM  @AgentStats 
    WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, ShiftDateAndTime)) = CAST('2017-03-07' AS DATETIME) 
) 
SELECT CONVERT(VARCHAR(10),Seconds/3600) 
     +':' 
     + RIGHT('00'+CONVERT(VARCHAR(2),(Seconds%3600)/60),2) 
     +':' 
     + RIGHT('00'+CONVERT(VARCHAR(2),Seconds%60),2) AS [HH:MM:SS] 
FROM acm; 

结果是：

HH:MM:SS 
34:14:08 

这里检查它：http://rextester.com/EHCSY33410

由于您只选择一天，因此您无需按ShiftDateAndTime进行分组。

UPDATE

我建议创建一个内联用户定义函数只是把它缩短并容易在一个大的查询使用。

CREATE FUNCTION dbo.SecondsToHMS(@Seconds int) 
RETURNS table 
AS 
RETURN 
(
    SELECT CONVERT(VARCHAR(20), @Seconds/3600) + ':' 
      + RIGHT('00' + CONVERT(VARCHAR(2), (@Seconds % 3600)/60) ,2) + ':' 
      + RIGHT('00' + CONVERT(VARCHAR(2), @Seconds%60), 2) AS [HMS] 
); 

GO 

那么前面的查询可以改为：

SELECT (SELECT HMS FROM dbo.SecondsToHMS(SUM (DATEDIFF(second, LoginTime, LogoutTime)))) [HMS] 
FROM  @AgentStats 
WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, ShiftDateAndTime)) = CAST('2017-03-07' AS DATETIME) 
GO 

/* 
|HMS  | 
|:-------| 
|34:14:08| 
*/ 

dbfiddle here