dbo.csp_JobLog_OK 'cmart001'



SELECT		machine,
		jobtask,
		operator,
		assist1,
		assist2,
		assist3,
		AVG(eff) AS eff,
		AVG(cpm) AS cpm,
		MAX(accum) AS accum,
		MAX(prod_date) AS t_stamp,
		SUM(CASE WHEN lunch = 0 AND paperwrk = 0 THEN runtime ELSE 0 END) AS prodtime,
		shift,
		serial,
		SUM(CASE WHEN lunch = 1 THEN runtime ELSE 0 END) AS lunch,
		SUM(CASE WHEN paperwrk = 1 THEN runtime ELSE 0 END) AS paperwork, 
		SUM(CASE WHEN stpbar = 0 AND lunch = 0 AND paperwrk = 0 THEN runtime ELSE 0 END) AS idle,
		SUM(CASE WHEN stpbar = 1 THEN runtime ELSE 0 END) AS runtime,
		CASE
			WHEN machine >= 1 AND machine <= 59 THEN 'Inserting'
			WHEN machine >= 60 AND machine <= 79 THEN 'Addressing and Ink Jetting'
			WHEN machine >= 80 AND machine <= 99 THEN 'Bindery'
			ELSE 'other'
		END AS type
FROM		dbo.Prodtrack
WHERE		operator <> 0
		AND jobtask <> 0
		AND accum <> 0
GROUP BY	machine,
		jobtask,
		operator,
		assist1,
		assist2,
		assist3,
		shift,
		serial