Advertisement
Guest User

Untitled

a guest
Feb 20th, 2019
65
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.32 KB | None | 0 0
  1. DECLARE @anoy int
  2. SET @anoy = YEAR(GETDATE())
  3. DECLARE @mesy int
  4. SET @mesy = YEAR(GETDATE()) - 1
  5.  
  6. SELECT SUM(total) total
  7. from (
  8. select sum(workOrder) as total from workOrder where workType = '02' and year(workOrderDate) = @mesy
  9. union all
  10. select sum(workOrder) as total from workOrder where workType= 'corretiva' and year(workOrderDate)= @mesy
  11. ) s;
  12.  
  13. WITH subresults AS
  14. ( SELECT MES = MONTH(workOrderDate),
  15. preventiva = (SUM(CASE WHEN WorkType = '02' AND workOrderDescription = 'preventiva' THEN 1.0 ELSE 0 END )),
  16. corretivas = (SUM(CASE WHEN workOrderDescription = 'CORRETIVA' THEN 1 ELSE 0 END)),
  17. total = SUM(CASE WHEN WorkType = '02' AND workOrderDescription = 'preventiva' THEN 1.0
  18. ELSE 0 END + CASE WHEN workOrderDescription = 'CORRETIVA' THEN 1 ELSE 0 END )
  19. FROM WorkOrder WHERE
  20. YEAR(workorderDate) = @mesy
  21. GROUP BY MONTH(workOrderDate))
  22. SELECT s.MES ,s.preventiva ,s.corretivas ,s.total,
  23. preventivasPct = CASE WHEN s.total <> 0 THEN(s.preventiva / s.total) * 100.0 ELSE NULL END,
  24. corretivasPct = CASE WHEN s.total <> 0 THEN(s.corretivas / s.total) * 100.0 ELSE NULL END
  25. FROM subresults s
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement