Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DECLARE @anoy int
- SET @anoy = YEAR(GETDATE())
- DECLARE @mesy int
- SET @mesy = YEAR(GETDATE()) - 1
- SELECT SUM(total) total
- from (
- select sum(workOrder) as total from workOrder where workType = '02' and year(workOrderDate) = @mesy
- union all
- select sum(workOrder) as total from workOrder where workType= 'corretiva' and year(workOrderDate)= @mesy
- ) s;
- WITH subresults AS
- ( SELECT MES = MONTH(workOrderDate),
- preventiva = (SUM(CASE WHEN WorkType = '02' AND workOrderDescription = 'preventiva' THEN 1.0 ELSE 0 END )),
- corretivas = (SUM(CASE WHEN workOrderDescription = 'CORRETIVA' THEN 1 ELSE 0 END)),
- total = SUM(CASE WHEN WorkType = '02' AND workOrderDescription = 'preventiva' THEN 1.0
- ELSE 0 END + CASE WHEN workOrderDescription = 'CORRETIVA' THEN 1 ELSE 0 END )
- FROM WorkOrder WHERE
- YEAR(workorderDate) = @mesy
- GROUP BY MONTH(workOrderDate))
- SELECT s.MES ,s.preventiva ,s.corretivas ,s.total,
- preventivasPct = CASE WHEN s.total <> 0 THEN(s.preventiva / s.total) * 100.0 ELSE NULL END,
- corretivasPct = CASE WHEN s.total <> 0 THEN(s.corretivas / s.total) * 100.0 ELSE NULL END
- FROM subresults s
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement