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