Converting DateDiff(s, x, x) results to HH:MM

Question

I've got the following SQL code that that will calculate, in seconds, the amount of OnProd and OffProd time assigned to each person by day:

SELECT 
    UserID,
    First_NM,
    Last_NM,
    MgrName,
    CASE WHEN PendReason = 'On Prod' THEN DATEDIFF(s, DateStart, DateEnd) ELSE 0 END AS OnProd,
    CASE WHEN PendReason = 'Off Prod' THEN DATEDIFF(s, DateStart, DateEnd) ELSE 0 END AS OffProd

INTO #ProdTmp   
FROM dbo.vw_mos_DPL_Reporting
WHERE PendReason LIKE '%Prod%'

This produces a table with this output (this is only an excerpt, which is why this output doesn't sum to the one below):

UserID  First_NM    Last_NM   MgrName       OnProd  OffProd
PATROI  PAULA       TROI      JOHN CASEY    127     0
AMOWIT  ANGELA      MOWITH    JOHN CASEY    11      0
AMOWIT  ANGELA      MOWITH    JOHN CASEY    0       60
AMOWIT  ANGELA      MOWITH    JOHN CASEY    28      0
AMOWIT  ANGELA      MOWITH    JOHN CASEY    0       24
AMOWIT  ANGELA      MOWITH    JOHN CASEY    0       9
PATROI  PAULA       TROI      JOHN CASEY    44      0
DXLANT  DONNA       LANTEL    JOHN CASEY    0       106
DXLANT  DONNA       LANTEL    JOHN CASEY    0       5
DXLANT  DONNA       LANTEL    JOHN CASEY    0       547

This is daily data, so each person has a record for each day for OnProd and OffProd data.

What I need to do is group this data up so it shows a single record for each user, and also creates a new field that sums up all On/Off Prod data.

This is what I have:

SELECT 
    UserID,
    First_NM,
    Last_NM,
    MgrName,
    SUM(OnProd)/60 as OnProd,
    SUM(OffProd)/60 as OffProd

FROM #ProdTmp
Group By 
    UserID,
    First_NM,
    Last_NM,
    MgrName

This is what it's giving me:

UserID  First_NM    Last_NM   MgrName       OnProd     OffProd
AMOWIT  ANGELA      MOWITH    JOHN CASEY    3          1
DXLANT  DONNA       LANTEL    JOHN CASEY    21         55
PATROI  PAULA       TROI      JOHN CASEY    51         2

So I have 2 questions here:

How do I turn those OnProd/OffProd numbers into a HH:MM format, and
How do I add a field that sums those 2 columns up and puts the result into a HH:MM format?

score 1 · Answer 1 · edited May 23 '17 at 12:10

1

Adding a field to sum up both columns is simple.

SUM(OnProd + OffProd) TotalTime

To convert seconds to hours and minutes use:

CONVERT(varchar, DATEADD(ms, MyColumn * 1000, 0), 108)

courtesy of How to convert Seconds to HH:MM:SS using T-SQL

edited May 23 '17 at 12:10

Community

1
1

answered Jun 20 '14 at 17:49

Vulcronos

3,428
3
16
24

score 1 · Accepted Answer · answered Jun 20 '14 at 17:49

1

SELECT UserID,
First_NM,
Last_NM,
MgrName,
CONVERT(varchar(5),  DATEADD(minute, SUM(OnProd)/60, 0), 114) as OnProd,
CONVERT(varchar(5),  DATEADD(minute, SUM(OffProd)/60, 0), 114) as OffProd, 
CONVERT(varchar(5),  DATEADD(minute, SUM(OnProd+OffProd)/60, 0), 114) as SumProd FROM #ProdTmp Group By UserID, First_NM, Last_NM,  MgrName

answered Jun 20 '14 at 17:49

TMNT2014

2,102
1
11
13

Both were great answers, but this was exactly what I needed. Worked perfectly, thanks! – Johnny Bones Jun 20 '14 at 18:10

Converting DateDiff(s, x, x) results to HH:MM

2 Answers2