Hello, I need a report meeting the conditions below.
-
of open work orders
-
of urgent corrective maintenance work orders > 5 days old
-
of routine corrective maintenance work orders > 14 days old
-
of routine PMs > 30 days old
-
of life safety PMs > 20 days old
- Other work orders > 30 days old (not included in 2-5 above; not routine corrective, urgent corrective, or PMs)
- Total of 2-6 (this is the # of work orders not within normal limits)
-
of day Open (or actually, days open would be better, if possible; today – date of oldest open work order, in # of days)
-
of work orders Completed in last 30 days
SQL
SELECT vWorkOrders.DateCreated AS ‘Date’,
- SUM(CASE WHEN (StatusCode = 'ACTIV ' OR StatusCode=‘PENDG’) THEN 1 ELSE 0 END) AS '# Active Work Orders’,
20 SUM(CASE WHEN (DateDiff(d,vWorkOrders.DateCreated,GETDATE()) >5) AND TypeCode='CM' AND PriorityCode=’2’ THEN 1 ELSE 0 END) AS '# Urgent Corrective Maintenance > 5 Days’,
3- SUM(CASE WHEN (DateDiff(d,vWorkOrders.DateCreated,GETDATE()) >14 Days) AND TypeCode='CM' AND PriorityCode=’3’ THEN 1 ELSE 0 END) AS '# Routine Corrective Maintenance > 14 Days’,
4- SUM(CASE WHEN (DateDiff(d,vWorkOrders.DateCreated,GETDATE()) >20 Days) AND TypeCode='PM' AND PriorityCode=’7’ THEN 1 ELSE 0 END) AS '# Life Safety Preventive Maintenance > 20 Days’,
5- SUM(CASE WHEN (DateDiff(d,vWorkOrders.DateCreated,GETDATE()) >30 Days) AND TypeCode='PM' AND PriorityCode=’3’ AND PriorityCode=’2’ THEN 1 ELSE 0 END) AS '#Preventive Maintenance > 30 Days’,
6- SUM(CASE WHEN (DateDiff(d,vWorkOrders.DateCreated,GETDATE()) >30 Days) AND TypeCode<>'PM' AND TypeCode<>'CM' AND PriorityCode<>’7’ AND PriorityCode<>’3’ AND PriorityCode<>’2’ THEN 1 ELSE 0 END) AS 'Other > 30 Days’,
FROM vWorkOrders
WHERE ( vWorkOrders.DateCreated BETWEEN '1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR) AND '12/31/' + CAST(YEAR(GETDATE()) AS VARCHAR) + ' 23:59')
AND (vWorkOrders.StatusCode = 'ACTIV' OR vWorkOrders.StatusCode = 'PENDG')
GROUP BY vWorkOrders.DateCreated ORDER BY vWorkOrders.DateCreated DESC