Expand|Select|Wrap|Line Numbers
- 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 are below
Expand|Select|Wrap|Line Numbers
- Codes TotalTime Units UPH Goal%
- HS-HY 14:07:00 69204 1114 72%
Expand|Select|Wrap|Line Numbers
- 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
Can not figure a way around this......
BTW
My data types for StartTime and FinishTime are varchar(8)