2
I need to add two fields in a query already existing in SQL SERVER, but I don’t know how to add them in an existing query. I will try to detail more what I want with the images below:
I have this table that gives me the monthly values and their percentage, what I need is: That before line 1, it gives me a total of last year’s calculations and among all the following values that already exist in this calculation, and also, the calculation of every year in effect in the last line, with all these values, I’ll leave the source code below so you can find out.
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
Can anyone help me? Thank you!