SQLTeam.com | Weblogs | Forums

Need Help - Write SQL Script using CASE, SUM etc


#1

Hello, I need a report meeting the conditions below.

  1. of open work orders

  2. of urgent corrective maintenance work orders > 5 days old

  3. of routine corrective maintenance work orders > 14 days old

  4. of routine PMs > 30 days old

  5. of life safety PMs > 20 days old

  6. Other work orders > 30 days old (not included in 2-5 above; not routine corrective, urgent corrective, or PMs)
  7. Total of 2-6 (this is the # of work orders not within normal limits)
  8. of day Open (or actually, days open would be better, if possible; today – date of oldest open work order, in # of days)

  9. of work orders Completed in last 30 days

SQL
SELECT vWorkOrders.DateCreated AS ‘Date’,

  1. 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