How to add two result fields in a query already formed in SQL

Asked

Viewed 41 times

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:

inserir a descrição da imagem aqui

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!

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.