SQL SUM Time of multiple rows

Question

I need some help on an SQL statement. I'm trying to SUM up time on 1 to 2 rows. So far the output looks like this:

workingdate    cin      typecin    cout    typecout   rn    sum
---------------------------------------------------------------------
2021-05-03     07:49    K          13:31   G          1     05:42:00
2021-05-03     14:01    K          16:17   G          2     02:16:00
2021-05-04     07:52    K          13:36   G          1     05:44:00
2021-05-04     14:04    K          16:22   G          2     02:18:00
...

This is my Code so far:

WITH CTE AS 
    (   SELECT chip_num, chip_user_id, chip_user,
            CONVERT(DATE,[date]) workingdate,
            (CASE WHEN [type] = 'K' THEN format([date], 'HH:mm', 'de-DE' ) ELSE NULL END) [cin],
            (CASE WHEN [type] = 'K' THEN type ELSE NULL END) [typecin],
            subtypein,
            (CASE WHEN [type] = 'G' THEN format([date], 'HH:mm', 'de-DE' ) ELSE NULL END) [cout],
            (CASE WHEN [type] = 'G' THEN type ELSE NULL END) [typecout],
            subtypeout,
            ROW_NUMBER() OVER(PARTITION BY chip_num,CONVERT(DATE,[date]), [type] ORDER BY [date]) rn
        FROM app_terminal1
    ),
    CTE1 AS
    (
        SELECT chip_num, chip_user_id, chip_user, workingdate, 
        LEFT(CONVERT(TIME(4),MIN([cin]),114),5) [cin],
        LEFT(CONVERT(VARCHAR(10),MIN([typecin]),114),5) [typecin],
        LEFT(CONVERT(VARCHAR(10),MAX([subtypein]),114),5) [subtypein],

        LEFT(CONVERT(TIME(4),MIN([cout]),114),5) [cout],
        LEFT(CONVERT(VARCHAR(10),MIN([typecout]),114),5) [typecout],
        LEFT(CONVERT(VARCHAR(10),MAX([subtypeout]),114),5) [subtypeout],

        FORMAT( DATEDIFF(MINUTE, LEFT(CONVERT(VARCHAR(10),MIN([cin]),114),5), LEFT(CONVERT(VARCHAR(10),MIN([cout]),114),5))/60, '00', 'en-US' ) as diffHour, 
        FORMAT( DATEDIFF(MINUTE, LEFT(CONVERT(VARCHAR(10),MIN([cin]),114),5), LEFT(CONVERT(VARCHAR(10),MIN([cout]),114),5)) 
        - (DATEDIFF(MINUTE, LEFT(CONVERT(VARCHAR(10),MIN([cin]),114),5), LEFT(CONVERT(VARCHAR(10),MIN([cout]),114),5))/60) * 60, 
        '00', 'en-US' )as diffMin,
        rn
        FROM CTE
        where chip_user_id = @ChipUserId and YEAR(workingdate) = @Year and MONTH(workingdate) = @Month 
        GROUP BY chip_num, chip_user_id, chip_user,workingdate,  rn
    ),
    CTE2 AS
    (
    select *, CONVERT(time,  case when diffHour IS NULL THEN NULL ELSE CONCAT(diffHour, ':', diffMin) end) as sum from CTE1
    )

    select * from CTE2

The output I need:

And I need something like this where the sum gets summed up for the whole day inside sumDay. But it is important to do something with the rn because the entries can vary. Sometimes there is only 1 row, or maybe 3 Rows!

workingdate    cin      typecin    cout    typecout   rn    sum         sumDay
-----------------------------------------------------------------------------
2021-05-03     07:49    K          13:31   G          1     05:42:00
2021-05-03     14:01    K          16:17   G          2     02:16:00    07:58:00
2021-05-04     07:52    K          13:36   G          1     05:44:00
2021-05-04     14:04    K          16:22   G          2     02:18:00    08:02:00
...

SAMPLE DATA:

Tablescheme:

name - varchar(255)
serial - varchar(255)
chip_user_id - varchar(255)
chip_user - varchar(255)
chip_num - varchar(255)
date - datetime
type - varchar(255)
subtypein(255)
subtypein(255)

Sample Insert into:

insert into app_terminal1 (name, serial, chip_user_id, chip_user, chip_num, date, type, subtypein, subtypeout)
VALUES 
('Test', 'Test',    '999',  'Test Test',    '9092090',  '2021-03-05 07:49:12.000',  'K',    NULL,   NULL),
('Test', 'Test',    '999',  'Test Test',    '9092090',  '2021-03-05 13:31:12.000',  'G',    NULL,   NULL),
('Test', 'Test',    '999',  'Test Test',    '9092090',  '2021-03-05 14:01:12.000',  'K',    NULL,   NULL),
('Test', 'Test',    '999',  'Test Test',    '9092090',  '2021-03-05 16:17:12.000',  'G',    NULL,   NULL),

('Test', 'Test',    '999',  'Test Test',    '9092090',  '2021-04-05 07:52:12.000',  'K',    NULL,   NULL),
('Test', 'Test',    '999',  'Test Test',    '9092090',  '2021-04-05 13:36:12.000',  'G',    NULL,   NULL),
('Test', 'Test',    '999',  'Test Test',    '9092090',  '2021-04-05 14:04:12.000',  'K',    NULL,   NULL),
('Test', 'Test',    '999',  'Test Test',    '9092090',  '2021-04-05 16:22:12.000',  'G',    NULL,   NULL)

Thanks in advance!

Squirrel · Accepted Answer

I make some changes to your query. Basically I avoid formatting date / time inside the query. If you need to format it to the required format, do it at the very last select statement, but not inside the cte, it complicates any calculation required on the date or time.

To calculate your sum column, use DATEDIFF() use MIN() and MAX() on [date]

DATEDIFF(MINUTE, MIN(CONVERT(TIME(0), [date])), MAX(CONVERT(TIME(0), [date])))

And to calculate the sum column and for sumDay use the window function

SUM(diffTotalMin) OVER (PARTITION BY workingdate)

To convert to the require time format HH:MM you can use DATEADD(MINUTE, diffTotalMin, 0)

WITH 
CTE AS
(
    SELECT  chip_num, chip_user_id, chip_user,
            CONVERT(DATE,[date]) AS workingdate, [date], [type],
            subtypein, subtypeout,
            ROW_NUMBER() OVER(PARTITION BY chip_num, CONVERT(DATE,[date]), [type] 
                                  ORDER BY [date]) AS rn
    FROM    app_terminal1
),
CTE1 AS
(
    SELECT  chip_num, chip_user_id, chip_user, workingdate,
            MIN(CONVERT(TIME(0), [date]))  AS cin,
            MIN(CASE WHEN [type] = 'K' then [type] end) AS [typecin],
            MAX(subtypein) AS subtypein,
            MAX(CONVERT(TIME(0), [date]))  AS cout,
            MIN(CASE WHEN [type] = 'G' then [type] end) AS [typecout],
            MAX(subtypeout) AS subtypeout,
            rn,
            DATEDIFF(MINUTE, MIN(CONVERT(TIME(0), [date])), 
                             MAX(CONVERT(TIME(0), [date]))) AS diffTotalMin
    FROM    CTE
    GROUP BY chip_num, chip_user_id, chip_user, workingdate, rn
),
CTE2 AS
(
    SELECT  *,
            diffTotalMin / 60 AS diffHour,
            diffTotalMin % 60 AS diffMin,
            CONVERT(TIME, DATEADD(MINUTE, diffTotalMin, 0)) AS [sum],
            CASE WHEN rn = COUNT(*) OVER (PARTITION BY chip_user_id , workingdate) 
                 THEN CONVERT(TIME, DATEADD(MINUTE, SUM(diffTotalMin) OVER (PARTITION BY workingdate), 0)) 
                 END AS [sumDay]
    FROM    CTE1
)
SELECT  *
FROM    CTE2
WHERE   chip_user_id = @ChipUserId 
AND     YEAR(workingdate) = @Year
AND     MONTH(workingdate) = @Month;

db<>fiddle demo

SQL SUM Time of multiple rows

Answers (2)

Related Questions