Adding time that totals more than 24 hrs in SQL

Question

I am trying to add time from several rows, and when they add up to more than hours I do not get correct results I am looking for. Would like to script TotalTime to allow more than 24 in HH. Does not have to be a datetime datatype.

StartTime and FinishTime are varchar(8) data types.

here is code and output where TotalTime is less than 24 hours and correct results

SELECT Codes = (DeptCode + '-' +  OpCode)
,TotalTime = convert(time(0),dateadd(second,sum(datediff(second,StartTime,FinishTime)),0)) 
,Units = SUM(Units)
,UPH = cast(isnull(sum(Units) / nullif(sum(datediff(minute,StartTime,FinishTime))*1.0,0),0.0)*60  as decimal(10,0))
,'Goal%' = (convert(varchar,cast((isnull(sum(Units) / nullif(sum(datediff(minute,StartTime,FinishTime))*1.0,0),0.0)*60)/5101*100 as decimal(10,0))) + '%') 
FROM PTW.dbo.TimeLog 
WHERE DeptCode = 'HP' AND OpCode = 'FC'
GROUP BY DeptCode, OpCode

Results are correct

Codes   TotalTime   Units   UPH   Goal%
HP-FC   12:37:00    47200   3741    73%

Here is actual breakdown to prove

ID#     Codes   TotalTime   Units   UPH     Goal%   AssociateName
---     -----   ---------   -----   ---     -----   -------------
2409193 HP-FC   00:21:00    2161    6174    121%    NAME
2507191 HP-FC   00:23:00    2000    5217    102%    NAME
90290   HP-FC   00:20:00    1704    5112    100%    NAME
31676   HP-FC   02:35:00    11234   4349    85%     NAME
2372437 HP-FC   01:50:00    7884    4300    84%     NAME
2378337 HP-FC   07:08:00    22217   3115    61%     NAME

So when I attempt this on a larger table with more rows of data this is what I get. Out put will show TotalTime as an incorrect time.

SELECT Codes = (DeptCode + '-' +  OpCode)
,TotalTime = convert(time(0),dateadd(second,sum(datediff(second,StartTime,FinishTime)),0))     
,Units = SUM(Units)
,UPH = cast(isnull(sum(Units) / nullif(sum(datediff(minute,StartTime,FinishTime))*1.0,0),0.0)*60  as decimal(10,0))
,'Goal%' = (convert(varchar,cast((isnull(sum(Units) / nullif(sum(datediff(minute,StartTime,FinishTime))*1.0,0),0.0)*60)/1552*100 as decimal(10,0))) + '%') 
FROM PTW.dbo.TimeLog
WHERE DeptCode = 'HS' AND OpCode = 'HY'
GROUP BY DeptCode, OpCode

Results from query are below. This can not be true time.

Codes   TotalTime   Units   UPH     Goal%
HS-HY   14:07:00    69204   1114    72%

Breakdown to show you that TotalTime should be more than 14:07:00

ID#     Codes   TotalTime   Units   UPH     Goal%   AssociateName
---     -----   ---------   -----   ---     -----   -------------
2377    HS-HY   11:25:00    20891   1830    118%    NAME
3476    HS-HY   04:50:00    6978    1444    93%     NAME
43864   HS-HY   12:20:00    17628   1429    92%     NAME
2372127 HS-HY   03:20:00    4748    1424    92%     NAME
2372129 HS-HY   07:00:00    9158    1308    84%     NAME
2422946 HS-HY   00:47:00    949     1211    78%     NAME
21437   HS-HY   06:02:00    6530    1082    70%     NAME
2372090 HS-HY   11:00:00    2322    211     14%     NAME
63448   HS-HY   03:43:00    0       0       0%      NAME
2372061 HS-HY   01:40:00    0       0       0%      NAME

Answer 1

For intervals that may exceed 24 hours you will need to concatenate a set of expressions that calculate each piece like so:

select 
    Codes     = (DeptCode + '-' + OpCode)
  , TotalTime = right('0' + convert(varchar(9),(sum(datediff(second,StartTime,FinishTime)) / 3600 )),2) + ':' 
              + right('0' + convert(varchar(2),(sum(datediff(second,StartTime,FinishTime)) / 60) % 60 ),2) + ':' 
              + right('0' + convert(varchar(2),(sum(datediff(second,StartTime,FinishTime)) % 60 )),2)
  , Units     = sum(Units)
  , UPH       = cast(isnull(sum(Units) / nullif(sum(datediff(minute, StartTime, FinishTime)) * 1.0, 0), 0.0) * 60 as decimal(10, 0))
  , [Goal%]   = (convert(varchar, cast((isnull(sum(Units) / nullif(sum(datediff(minute, StartTime, FinishTime)) * 1.0, 0), 0.0) * 60) / 5101 * 100 as decimal(10, 0))) + '%')
from ptw.dbo.TimeLog
where DeptCode = 'HP'
  and OpCode = 'FC'
group by 
    DeptCode
  , OpCode

---

Also, you should not use string literals for aliases, for aliases that do not conform to the rules for regular identifiers, enclose them in square brackets instead of single quotes.

• Database Identifiers - docs