Select Sum HH:MM for period between two dates

Question

I would like to get the sum of hours (Duration) worked between say 16/11/2016 09:00 > 15/12/2016 17:30. To achieve this I need to calculate the Duration for each day worked between the two dates and then to add those up to get monthly hours worked.

The EventTime table will contain data for every time a user clock in when they start work, clocks out at lunch time, clocks back in after lunch, and then clocks out at the end of the day.

I have managed to get this to work for a single day:

EMPLOYEE    CLOCK IN            CLOCK OUT           DURATION
Tatjana     05/01/2017 08:33    05/01/2017 13:12    04:39
Harj        05/01/2017 10:59    05/01/2017 14:20    03:20
Tomasz      05/01/2017 09:55        
John        05/01/2017 09:57        
Sam         05/01/2017 08:11    05/01/2017 14:11    05:59
Paul        05/01/2017 09:39    05/01/2017 14:05    04:26
Adrian      05/01/2017 13:59        
Sophie      05/01/2017 08:42        
Meg         05/01/2017 07:56    05/01/2017 13:10    05:14
Anna        05/01/2017 07:59    05/01/2017 12:30    04:31
Adriana     05/01/2017 07:46    05/01/2017 12:44    04:58
Jacky       05/01/2017 09:01        
Anna        05/01/2017 07:57    05/01/2017 12:29    04:32
Kelly       05/01/2017 07:56    05/01/2017 12:45    04:48
Ana         05/01/2017 07:41    05/01/2017 14:13    06:32

The above is achieved using the following query:

SELECT
    u.Field14_50 AS EmployeeID,
    u.Firstname,
    u.Surname,
    MIN(e.EventTime) AS [Clocked In],
    CASE 
        WHEN MAX(e.EventTime) = MIN(e.EventTime) THEN NULL
        WHEN MAX(e.EventTime) > MIN(e.EventTime) THEN MAX(e.EventTime)
    END AS [Clocked Out],
    CASE
        WHEN MAX(e.EventTime) = MIN(e.EventTime) THEN NULL
        WHEN MAX(e.EventTime) > MIN(e.EventTime) THEN FORMAT((DATEDIFF(SECOND, MIN(e.EventTime), MAX(e.EventTime)) / 3600) % 24,  '00') + ':' + FORMAT((DATEDIFF(SECOND, MIN(e.EventTime), MAX(e.EventTime)) / 60) % 60, '00')
    END AS [Duration]
FROM
    UsersEx AS u
INNER JOIN
    EventsEx AS e
ON
    u.UserID = e.UserID
WHERE
    u.Field14_50 <> ''
AND
    u.DepartmentName IN (
        'Production',
        'Finance and Administration',
        'Purchase',
        'Sales',
        'Warehouse'
    )
AND
    DAY(e.EventTime) = DAY(GETDATE())     AND     MONTH(e.EventTime) = MONTH(GETDATE())      AND     YEAR(e.EventTime) = YEAR(GETDATE())
AND
    e.PeripheralName IN ('TIME AND ATTENDANCE OFFICE (In)', 'TIME AND ATTENDANCE OFFICE (Out)')
GROUP BY
    u.Field14_50,
    u.UserID,
    u.FirstName,
    u.Surname
ORDER BY
    u.Surname ASC

I hope that is clear.

Thanks in advance.

Update: 06/01/2017

I am able to use the following query:

SELECT
    u.FirstName,
    e.EventTime
FROM
    UsersEx AS u    
INNER JOIN
    EventsEx AS e
ON
    u.UserID = e.UserID
WHERE
    u.Field14_50 <> ''
AND
    e.EventTime > '2016/11/16'     AND     e.EventTime < '2016/12/16'
AND
    e.PeripheralName IN ('TIME AND ATTENDANCE OFFICE (In)', 'TIME AND ATTENDANCE OFFICE (Out)')
AND
    u.DepartmentName IN ('Finance and Administration')
ORDER BY
    u.Field14_50,
    e.EventTime
ASC

to get this result:

Ram 16/11/2016 09:12
Ram 16/11/2016 12:59
Ram 16/11/2016 13:39
Ram 16/11/2016 17:47
Ram 17/11/2016 09:35
Ram 17/11/2016 12:45
Ram 17/11/2016 13:11
Ram 17/11/2016 17:43
Ram 21/11/2016 09:14
Ram 21/11/2016 12:24
Ram 21/11/2016 12:53
Ram 21/11/2016 17:36
Ram 22/11/2016 09:18
Ram 22/11/2016 13:32
Ram 22/11/2016 17:45
Ram 23/11/2016 09:10
Ram 23/11/2016 13:13
Ram 23/11/2016 13:51
Ram 24/11/2016 09:10
Ram 24/11/2016 13:15
Ram 24/11/2016 13:50
Ram 24/11/2016 17:41
Ram 25/11/2016 09:12
Ram 25/11/2016 17:36
Ram 28/11/2016 09:05
Ram 28/11/2016 12:32
Ram 28/11/2016 13:12
Ram 28/11/2016 17:40
Ram 29/11/2016 09:17
Ram 29/11/2016 12:45
Ram 29/11/2016 13:16
Ram 29/11/2016 17:50
Ram 30/11/2016 09:15
Ram 30/11/2016 12:51
Ram 30/11/2016 13:55
Ram 30/11/2016 17:31
Ram 01/12/2016 09:10
Ram 01/12/2016 12:44
Ram 01/12/2016 13:12
Ram 01/12/2016 17:36
Ram 02/12/2016 09:00
Ram 02/12/2016 12:19
Ram 02/12/2016 12:51
Ram 02/12/2016 17:38
Ram 05/12/2016 09:14
Ram 05/12/2016 12:45
Ram 05/12/2016 13:28
Ram 05/12/2016 17:45
Ram 06/12/2016 09:32
Ram 06/12/2016 12:15
Ram 06/12/2016 12:49
Ram 06/12/2016 17:51
Ram 07/12/2016 09:09
Ram 07/12/2016 12:43
Ram 07/12/2016 13:22
Ram 07/12/2016 17:51
Ram 08/12/2016 09:18
Ram 08/12/2016 12:54
Ram 08/12/2016 13:16
Ram 08/12/2016 17:39
Ram 09/12/2016 09:09
Ram 09/12/2016 18:02
Ram 12/12/2016 09:20
Ram 12/12/2016 12:55
Ram 12/12/2016 13:20
Ram 12/12/2016 17:47
Ram 13/12/2016 09:13
Ram 13/12/2016 13:10
Ram 13/12/2016 13:37
Ram 13/12/2016 18:01
Ram 15/12/2016 09:07
Ram 15/12/2016 12:37
Ram 15/12/2016 13:12
Ram 15/12/2016 17:53
Yuka    16/11/2016 08:52
Yuka    16/11/2016 19:05
Yuka    17/11/2016 09:02
Yuka    17/11/2016 18:25
Yuka    18/11/2016 08:23
Yuka    18/11/2016 18:26
Yuka    21/11/2016 08:12
Yuka    21/11/2016 17:59
Yuka    22/11/2016 08:51
Yuka    22/11/2016 17:44
Yuka    23/11/2016 08:43
Yuka    23/11/2016 18:07
Yuka    24/11/2016 08:42
Yuka    24/11/2016 18:24
Yuka    25/11/2016 08:37
Yuka    25/11/2016 17:34
Yuka    28/11/2016 08:44
Yuka    28/11/2016 18:03
Yuka    29/11/2016 08:11
Yuka    29/11/2016 16:58
Yuka    12/12/2016 08:51
Yuka    12/12/2016 17:57
Yuka    13/12/2016 07:51
Yuka    13/12/2016 18:30
Yuka    14/12/2016 08:32
Yuka    14/12/2016 18:04
Yuka    15/12/2016 08:40
Yuka    15/12/2016 18:09
Duncan  16/11/2016 07:25
Duncan  16/11/2016 18:28
Duncan  17/11/2016 07:25
Duncan  17/11/2016 17:48
Duncan  18/11/2016 07:29
Duncan  21/11/2016 07:33
Duncan  21/11/2016 17:48
Duncan  22/11/2016 07:31
Duncan  22/11/2016 18:14
Duncan  23/11/2016 07:43
Duncan  24/11/2016 07:21
Duncan  25/11/2016 07:32
Duncan  28/11/2016 07:35
Duncan  28/11/2016 18:11
Duncan  29/11/2016 07:34
Duncan  30/11/2016 07:35
Duncan  30/11/2016 18:21
Duncan  01/12/2016 07:27
Duncan  01/12/2016 17:57
Duncan  02/12/2016 07:38
Duncan  05/12/2016 07:29
Duncan  05/12/2016 18:12
Duncan  06/12/2016 07:28
Duncan  06/12/2016 17:37
Duncan  07/12/2016 07:13
Duncan  07/12/2016 07:19
Duncan  07/12/2016 18:01
Duncan  08/12/2016 07:22
Duncan  08/12/2016 17:56
Duncan  09/12/2016 07:24
Duncan  09/12/2016 17:30

Now how can I get the MIN and MAX for each user for each day from that result set (as below)?

Employee   Clocked In         Clocked Out        Duration
Ram        16/11/2016 09:12   16/11/2016 17:47   08:35
Ram        17/11/2016 09:35   17/11/2016 17:43   08:08
... 
Ram        13/12/2016 09:13   13/12/2016 18:01   08:48
Ram        15/12/2016 09:07   15/12/2016 17:53   08:46

and so on for each employee...

You should convert to seconds each Duration and then convert to format HH:MM http://stackoverflow.com/questions/1262497/how-to-convert-seconds-to-hhmmss-using-t-sql — JotaPardo, Jan 05 '17 at 15:08
Can you give me a feedback? I posted a possible solution. Please check it — JotaPardo, Jan 05 '17 at 17:11
You added a new request. Even so check the update to my answer and verify that it works for you — JotaPardo, Jan 06 '17 at 12:38

JotaPardo · Answer 1 · 2017-01-06T12:50:05.587

Convert each Duration field to seconds and then add it to the format HH:MM

This is an example:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
    DROP TABLE #tmp

CREATE TABLE #tmp (
    Id INT IDENTITY
    , duration VARCHAR(MAX)
    )

INSERT INTO #tmp (duration)
VALUES ('04:39')
    , ('03:20')
    , ('05:59')

SELECT 
    Sum(Left(duration, 2) * 3600 + substring(duration, 4, 2) * 60) AS seconds
    , CONVERT(VARCHAR, DATEADD(ms, (Sum(Left(duration, 2) * 3600 + substring(duration, 4, 2) * 60)) * 1000, 0), 114) AS 'seconds to HH:MM'
    --http://stackoverflow.com/questions/1262497/how-to-convert-seconds-to-hhmmss-using-t-sql
FROM #tmp

DROP TABLE #tmp

RESULT

seconds     seconds to HH:MM
----------- ------------------------------
50280       13:58:00:000

(1 row(s) affected)

One option to implement in your query this solution would be to use a CTE. In this case with a CTE would look something like this:

;WITH Base
AS (
    SELECT u.Field14_50 AS EmployeeID
        , u.Firstname
        , u.Surname
        , MIN(e.EventTime) AS [Clocked In]
        , CASE 
            WHEN MAX(e.EventTime) = MIN(e.EventTime)
                THEN NULL
            WHEN MAX(e.EventTime) > MIN(e.EventTime)
                THEN MAX(e.EventTime)
            END AS [Clocked Out]
        , CASE 
            WHEN MAX(e.EventTime) = MIN(e.EventTime)
                THEN NULL
            WHEN MAX(e.EventTime) > MIN(e.EventTime)
                THEN FORMAT((DATEDIFF(SECOND, MIN(e.EventTime), MAX(e.EventTime)) / 3600) % 24, '00') + ':' + FORMAT((DATEDIFF(SECOND, MIN(e.EventTime), MAX(e.EventTime)) / 60) % 60, '00')
            END AS [Duration]
    FROM UsersEx AS u
        INNER JOIN EventsEx AS e
            ON u.UserID = e.UserID
    WHERE u.Field14_50 <> ''
        AND u.DepartmentName IN (
            'Production'
            , 'Finance and Administration'
            , 'Purchase'
            , 'Sales'
            , 'Warehouse'
            )
        AND DAY(e.EventTime) = DAY(GETDATE())
        AND MONTH(e.EventTime) = MONTH(GETDATE())
        AND YEAR(e.EventTime) = YEAR(GETDATE())
        AND e.PeripheralName IN (
            'TIME AND ATTENDANCE OFFICE (In)'
            , 'TIME AND ATTENDANCE OFFICE (Out)'
            )
    GROUP BY u.Field14_50
        , u.UserID
        , u.FirstName
        , u.Surname
    --ORDER BY u.Surname ASC --Don't include ORDER BY here.
    )
SELECT CONVERT(VARCHAR, DATEADD(ms, (Sum(Left(duration, 2) * 3600 + substring(duration, 4, 2) * 60)) * 1000, 0), 114) AS 'sum of hours (Duration)'
FROM Base
ORDER BY u.Surname

UPDATED

According to your update, you would need to query using the MIN and MAX aggregate functions, but performing a GROUP BY for dates converted to DATE. With this you can get the highest and lowest value for each day.

CODE:

SELECT u.FirstName
    , MIN(e.EventTime) AS 'Clocked In'
    , CASE 
        WHEN MIN(e.EventTime) = MAX(e.EventTime)
            THEN NULL
        ELSE MAX(e.EventTime)
        END AS 'Clocked Out'
    , DATEDIFF(SECOND, min(e.EventTime), MAX(e.EventTime)) AS 'Duration in seconds'
    , CONVERT(VARCHAR, DATEADD(ms, DATEDIFF(SECOND, min(e.EventTime), MAX(e.EventTime)) * 1000, 0), 114) AS 'Duration'
FROM UsersEx AS u
    INNER JOIN EventsEx AS e
        ON u.UserID = e.UserID
WHERE u.Field14_50 <> ''
    AND e.EventTime > '2016/11/16'
    AND e.EventTime < '2016/12/16'
    AND e.PeripheralName IN (
        'TIME AND ATTENDANCE OFFICE (In)'
        , 'TIME AND ATTENDANCE OFFICE (Out)'
        )
    AND u.DepartmentName IN ('Finance and Administration')
GROUP BY u.FirstName
    , cast(e.EventTime AS DATE)
ORDER BY u.FirstName

Then, you only need to sum the seconds and convert them to the HH:MM format that I showed you, using the CTE or a Subquery.

Thank you for your response to my question. Unfortunately I have restricted access to the database and so cannot create temporary tables etc. I guess I should have mentioned that in the first place. Apologies for that. — Ram, Jan 06 '17 at 09:35
@Ram The use of temporary tables is just an example. In your case, if you do not have permissions you should only try the second block of code where I use the CTE. — JotaPardo, Jan 06 '17 at 12:15

Select Sum HH:MM for period between two dates

1 Answers1