Add up Total Cumulative Hours, Minutes, Seconds from shift times for all agents in SQL

Question

I need to calculate total 'Logged in' times for all agents.

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

I want to calculate total cumulative man hours for 2017-03-07 across all agents. For example,

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

I need the time in hh:mm:ss. I'm using SQL Server 2014 to run the query.

I have tried:

SELECT
(SUM (datediff(second, LoginTime, LogoutTime)) / 3600) [TotalTime]
FROM AgentStats

But I only get the time in minutes. I need it in hh:mm:ss.

Answer 1

I've used a CTE just to make it easy to format the result as 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;

The result is:

HH:MM:SS
34:14:08

Check it here: http://rextester.com/EHCSY33410

You don't need to group by ShiftDateAndTime due you're selecting only one day.

UPDATE

I'd suggest to create an INLINE User Defined Function just to make it shorter and easy to use on a big queries.

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

Then previous query can be changed to:

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