none
CALCULO DE PORCENTAGEM COM CONDIÇÕES EXTERNAS RRS feed

  • Pergunta

  • Boa tarde,

    Estou com um código que me traz os valores dos campos da tabela, porem quando eu " *100/ " ele me dá tudo 100% sendo que o valor seria a quantidades de ordserv. geradas / ordserv.fechadas e depois multiplicada por 100 pra dar o percentual. Segue o código abaixo:

    SELECT DATEPART(MM, ORDSERV.DATPRO2) MES, DATEPART(YY, ORDSERV.DATPRO2) MES, 
    ENGEMAN.MESABREV(ORDSERV.DATPRO2)+'/'+SUBSTRING(CAST(DATEPART(YY, ORDSERV.DATPRO2) AS VARCHAR),3,2) MES_ANO,


    SUM(CASE WHEN ORDSERV.STATORD='F' THEN 1 ELSE 0 END) 'REALIZADAS'

    FROM ORDSERV

    WHERE ORDSERV.DATPRO2 BETWEEN '01/03/2016' AND '30/04/2016'
    AND ORDSERV.STATORD='F'
    AND ORDSERV.CODEMP=76
    AND 'S' IN (SELECT REGSERV.EXECUTADO FROM REGSERV WHERE REGSERV.CODEMP=ORDSERV.CODEMP AND REGSERV.CODORD=ORDSERV.CODORD) 
    GROUP BY DATEPART(MM, ORDSERV.DATPRO2),ENGEMAN.MESABREV(ORDSERV.DATPRO2), DATEPART(YYYY, ORDSERV.DATPRO2)
    ORDER BY 1,2

    Resultado: 

    MES MES_1 MES_ANO REALIZADAS 
    --- ----- ------- ---------- 
    3 2016 mar/16 489 
    4 2016 abr/16 659 

    Esse é o resultado que ele me dá em valores, está correto, total de realizadas.

    SELECT DATEPART(MM, ORDSERV.DATPRO2) MES, DATEPART(YY, ORDSERV.DATPRO2) MES, 
    ENGEMAN.MESABREV(ORDSERV.DATPRO2)+'/'+SUBSTRING(CAST(DATEPART(YY, ORDSERV.DATPRO2) AS VARCHAR),3,2) MES_ANO,


    COUNT(*) 'GERADAS'

    FROM ORDSERV

    WHERE ORDSERV.DATPRO2 BETWEEN '01/03/2016' AND '30/04/2016'
    AND ORDSERV.STATORD='F'
    AND ORDSERV.CODEMP=76 
    GROUP BY DATEPART(MM, ORDSERV.DATPRO2),ENGEMAN.MESABREV(ORDSERV.DATPRO2), DATEPART(YYYY, ORDSERV.DATPRO2)
    ORDER BY 1,2

    Resultado:

    MES MES_1 MES_ANO GERADAS 
    --- ----- ------- ------- 
    3 2016 mar/16 514 
    4 2016 abr/16 707 

    Esse é o resultado que ele me dá em valores, está correto, total de geradas. 
    Quando eu peço pra ele me dá em porcentagem ele me trás tudo 100%.

    SELECT DATEPART(MM, ORDSERV.DATPRO2) MES, DATEPART(YY, ORDSERV.DATPRO2) MES, 
    ENGEMAN.MESABREV(ORDSERV.DATPRO2)+'/'+SUBSTRING(CAST(DATEPART(YY, ORDSERV.DATPRO2) AS VARCHAR),3,2) MES_ANO,


    SUM(CASE WHEN ORDSERV.STATORD='F' THEN 1 ELSE 0 END)*100/COUNT(*) AS PORCENTAGEM

    FROM ORDSERV

    WHERE ORDSERV.DATPRO2 BETWEEN '01/03/2016' AND '30/04/2016'
    AND ORDSERV.STATORD='F'
    AND ORDSERV.CODEMP=76
    AND 'S' IN (SELECT REGSERV.EXECUTADO FROM REGSERV WHERE REGSERV.CODEMP=ORDSERV.CODEMP AND REGSERV.CODORD=ORDSERV.CODORD) 
    GROUP BY DATEPART(MM, ORDSERV.DATPRO2),ENGEMAN.MESABREV(ORDSERV.DATPRO2), DATEPART(YYYY, ORDSERV.DATPRO2)
    ORDER BY 1,2

    Resultado:

    MES MES_1 MES_ANO PORCENTAGEM 
    --- ----- ------- ----------- 
    3 2016 mar/16 100 
    4 2016 abr/16 100 

    Creio que o erro está na condição "AND 'S' IN (SELECT REGSERV.EXECUTADO FROM REGSERV WHERE REGSERV.CODEMP=ORDSERV.CODEMP AND REGSERV.CODORD=ORDSERV.CODORD) " , mas é com ele que tiro os resultados da tabela REGSERV e trago para a ORDSERV me dando o todas das realizadas.
    Peço ajuda de vocês pois já estou uma semana tentando resolver isso e nada. Qualquer ajuda é bem vinda. Desde já agradeço a todos.
    quarta-feira, 11 de maio de 2016 17:31

Respostas

  • Boa tarde,

    Leonardo, experimente fazer uns testes dessa forma:

    SELECT DATEPART(MM, ORDSERV.DATPRO2) MES, DATEPART(YY, ORDSERV.DATPRO2) MES, 
    ENGEMAN.MESABREV(ORDSERV.DATPRO2)+'/'+SUBSTRING(CAST(DATEPART(YY, ORDSERV.DATPRO2) AS VARCHAR),3,2) MES_ANO,
    
    
    100.0 * SUM(CASE WHEN ORDSERV.STATORD = 'F' AND OA.EXECUTADO = 'S' THEN 1 ELSE 0 END) / COUNT(*) AS PORCENTAGEM
    
    FROM ORDSERV
    OUTER APPLY
    (
        SELECT REGSERV.EXECUTADO FROM REGSERV 
        WHERE REGSERV.CODEMP=ORDSERV.CODEMP AND REGSERV.CODORD=ORDSERV.CODORD
    ) AS OA
    
    
    WHERE ORDSERV.DATPRO2 BETWEEN '01/03/2016' AND '30/04/2016'
    AND ORDSERV.STATORD='F'
    AND ORDSERV.CODEMP=76 
    GROUP BY DATEPART(MM, ORDSERV.DATPRO2),ENGEMAN.MESABREV(ORDSERV.DATPRO2), DATEPART(YYYY, ORDSERV.DATPRO2)
    ORDER BY 1,2
    

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    • Marcado como Resposta Marcos SJ quinta-feira, 12 de maio de 2016 19:04
    quarta-feira, 11 de maio de 2016 18:37
  • Boa tarde,

    Não sei se entendi corretamente, mas experimente fazer uns testes alterando apenas o trecho do Outer Apply:

    OUTER APPLY
    (
        SELECT MAX(REGSERV.EXECUTADO) AS EXECUTADO FROM REGSERV 
        WHERE REGSERV.CODEMP=ORDSERV.CODEMP AND REGSERV.CODORD=ORDSERV.CODORD
    ) AS OA

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    • Marcado como Resposta Marcos SJ quinta-feira, 12 de maio de 2016 19:44
    quarta-feira, 11 de maio de 2016 20:13

Todas as Respostas

  • Boa tarde Leonardo.

    Tenta na linha do sum usar dessa forma: SUM((CASE WHEN ORDSERV.STATORD='F' THEN 1 ELSE 0 END)*100)/COUNT(*) AS PORCENTAGEM

    Senão conseguir tente fazer dessa forma.

    with tabela as (
    SELECT DATEPART(MM, ORDSERV.DATPRO2) MES, DATEPART(YY, ORDSERV.DATPRO2) Ano, 
    ENGEMAN.MESABREV(ORDSERV.DATPRO2)+'/'+SUBSTRING(CAST(DATEPART(YY, ORDSERV.DATPRO2) AS VARCHAR),3,2) MES_ANO,
    SUM(CASE WHEN ORDSERV.STATORD='F' THEN 1 ELSE 0 END) AS PORCENTAGEM
    FROM ORDSERV
    WHERE ORDSERV.DATPRO2 BETWEEN '01/03/2016' AND '30/04/2016'
    AND ORDSERV.STATORD='F'
    AND ORDSERV.CODEMP=76
    AND 'S' IN (SELECT REGSERV.EXECUTADO FROM REGSERV WHERE REGSERV.CODEMP=ORDSERV.CODEMP AND REGSERV.CODORD=ORDSERV.CODORD) 
    GROUP BY DATEPART(MM, ORDSERV.DATPRO2),ENGEMAN.MESABREV(ORDSERV.DATPRO2), DATEPART(YYYY, ORDSERV.DATPRO2)
    ORDER BY 1,2)
    
    
    select MES, Ano, MES_ANO, (PORCENTAGEM * 100)/count(*) as Porcentagem FROM tabela
    group by MES, Ano, MES_ANO

    Att..




    • Editado Andre Modena quarta-feira, 11 de maio de 2016 18:16
    quarta-feira, 11 de maio de 2016 17:52
  • Boa tarde Andre,

    Desde já agradeço sua atenção e vontade em me ajudar.

    A primeira opção com SUM os resultados retornaram os mesmos todos 100%.

    MES  MES_1  MES_ANO  PORCENTAGEM  
    ---  -----  -------  -----------  
      3      2016     mar/16           100  
      4      2016      abr/16           100  

    Na  segunda opção os resultados vieram 0%.

    with tabela as (
    SELECT DATEPART(MM, ORDSERV.DATPRO2) MES, DATEPART(YY, ORDSERV.DATPRO2) Ano, 
    ENGEMAN.MESABREV(ORDSERV.DATPRO2)+'/'+SUBSTRING(CAST(DATEPART(YY, ORDSERV.DATPRO2) AS VARCHAR),3,2) MES_ANO,
    SUM(CASE WHEN ORDSERV.STATORD='F' THEN 1 ELSE 0 END)*100/COUNT(*) AS PORCENTAGEM
    FROM ORDSERV
    WHERE ORDSERV.DATPRO2 BETWEEN '01/03/2016' AND '30/04/2016'
    AND ORDSERV.STATORD='F'
    AND ORDSERV.CODEMP=76
    AND 'S' IN (SELECT REGSERV.EXECUTADO FROM REGSERV WHERE REGSERV.CODEMP=ORDSERV.CODEMP AND REGSERV.CODORD=ORDSERV.CODORD) 
    GROUP BY DATEPART(MM, ORDSERV.DATPRO2),ENGEMAN.MESABREV(ORDSERV.DATPRO2), DATEPART(YYYY, ORDSERV.DATPRO2)
    ORDER BY 1,2)
    
    
    select MES, Ano, MES_ANO, count(PORCENTAGEM)/100 as Porcentagem FROM tabela
    group by MES, Ano, MES_ANO

    MES   Ano  MES_ANO  Porcentagem  
    ---  ----  -------  -----------  
      3     2016   mar/16              0  
      4     2016    abr/16              0  

    Declarando variáreis eu consegui apenas retornar um valor, não retomando pelos meses. Consigo os valores de um determinado mês, mas não pelos meses ou por um período anual. Se você conseguir algo dentro dessas variáveis me retornando a porcentagem pelos meses, ficarei muito grato.

     DECLARE @REALIZADAS INT, @GERADAS INT, @EM_ABERTO INT, @FECHADAS INT, @CANCELADAS INT 
      SELECT @REALIZADAS =                         
     (SELECT 
            SUM(CASE WHEN ORDSERV.STATORD='F' THEN 1 ELSE 0 END) 'REALIZADAS'


     FROM ORDSERV
       INNER JOIN TIPMANUT ON TIPMANUT.CODTIPMAN=ORDSERV.CODTIPMAN AND TIPMANUT.CODEMP=ORDSERV.CODEMP_2 
      INNER JOIN SETEXE ON SETEXE.CODSET=ORDSERV.CODSET AND SETEXE.CODEMP=ORDSERV.CODEMP_4
      LEFT OUTER JOIN APLIC ON APLIC.CODAPL=ORDSERV.CODAPL
      LEFT OUTER JOIN FUNC ON FUNC.CODFUN=ORDSERV.CODFUN_RESP      
      LEFT OUTER JOIN PLAMANUT ON ORDSERV.CODPLA=PLAMANUT.CODPLA AND ORDSERV.CODEMP_1=PLAMANUT.CODEMP
      LEFT OUTER JOIN FILIAL ON FILIAL.CODFIL=ORDSERV.CODFIL AND FILIAL.CODEMP=ORDSERV.CODEMP_7  
      LEFT OUTER JOIN CLIENTE ON CLIENTE.CODCLI=ORDSERV.CODCLI


      WHERE ORDSERV.DATPRO2 BETWEEN '01/04/2016' AND '30/04/2016'
       AND ORDSERV.STATORD='F'
       AND ORDSERV.CODEMP=76
       AND 'S' IN (SELECT REGSERV.EXECUTADO FROM REGSERV WHERE REGSERV.CODEMP=ORDSERV.CODEMP AND REGSERV.CODORD=ORDSERV.CODORD))

       SELECT @GERADAS = 
       (SELECT  COUNT(*) GERADAS
       FROM ORDSERV
       WHERE ORDSERV.DATPRO2 BETWEEN '01/04/2016' AND '30/04/2016'
       AND ORDSERV.STATORD='F'
       AND ORDSERV.CODEMP=76)

        SELECT @FECHADAS =
      (SELECT SUM(CASE WHEN ORDSERV.STATORD= 'F'THEN 1 ELSE 0 END) 'FECHADAS'

    FROM ORDSERV
      INNER JOIN TIPMANUT ON TIPMANUT.CODTIPMAN=ORDSERV.CODTIPMAN AND TIPMANUT.CODEMP=ORDSERV.CODEMP_2 
      INNER JOIN SETEXE ON SETEXE.CODSET=ORDSERV.CODSET AND SETEXE.CODEMP=ORDSERV.CODEMP_4
      LEFT OUTER JOIN APLIC ON APLIC.CODAPL=ORDSERV.CODAPL
      LEFT OUTER JOIN FUNC ON FUNC.CODFUN=ORDSERV.CODFUN_RESP      
      LEFT OUTER JOIN PLAMANUT ON ORDSERV.CODPLA=PLAMANUT.CODPLA AND ORDSERV.CODEMP_1=PLAMANUT.CODEMP
      LEFT OUTER JOIN FILIAL ON FILIAL.CODFIL=ORDSERV.CODFIL AND FILIAL.CODEMP=ORDSERV.CODEMP_7  
      LEFT OUTER JOIN CLIENTE ON CLIENTE.CODCLI=ORDSERV.CODCLI

    WHERE ORDSERV.DATPRO2 BETWEEN '01/04/2016' AND '30/04/2016'
      AND ORDSERV.CODEMP=76)  

       SELECT @CANCELADAS =
      (SELECT SUM(CASE WHEN ORDSERV.STATORD= 'C'THEN 1 ELSE 0 END) 'CANCELADAS'

    FROM ORDSERV
      INNER JOIN TIPMANUT ON TIPMANUT.CODTIPMAN=ORDSERV.CODTIPMAN AND TIPMANUT.CODEMP=ORDSERV.CODEMP_2 
      INNER JOIN SETEXE ON SETEXE.CODSET=ORDSERV.CODSET AND SETEXE.CODEMP=ORDSERV.CODEMP_4
      LEFT OUTER JOIN APLIC ON APLIC.CODAPL=ORDSERV.CODAPL
      LEFT OUTER JOIN FUNC ON FUNC.CODFUN=ORDSERV.CODFUN_RESP      
      LEFT OUTER JOIN PLAMANUT ON ORDSERV.CODPLA=PLAMANUT.CODPLA AND ORDSERV.CODEMP_1=PLAMANUT.CODEMP
      LEFT OUTER JOIN FILIAL ON FILIAL.CODFIL=ORDSERV.CODFIL AND FILIAL.CODEMP=ORDSERV.CODEMP_7  
      LEFT OUTER JOIN CLIENTE ON CLIENTE.CODCLI=ORDSERV.CODCLI

    WHERE ORDSERV.DATPRO2 BETWEEN '01/04/2016' AND '30/04/2016'
      AND ORDSERV.CODEMP=76) 

      SELECT @EM_ABERTO =
       (SELECT SUM(CASE WHEN ORDSERV.STATORD= 'A'THEN 1 ELSE 0 END) 'EM_ABERTO'

    FROM ORDSERV
      INNER JOIN TIPMANUT ON TIPMANUT.CODTIPMAN=ORDSERV.CODTIPMAN AND TIPMANUT.CODEMP=ORDSERV.CODEMP_2 
      INNER JOIN SETEXE ON SETEXE.CODSET=ORDSERV.CODSET AND SETEXE.CODEMP=ORDSERV.CODEMP_4
      LEFT OUTER JOIN APLIC ON APLIC.CODAPL=ORDSERV.CODAPL
      LEFT OUTER JOIN FUNC ON FUNC.CODFUN=ORDSERV.CODFUN_RESP      
      LEFT OUTER JOIN PLAMANUT ON ORDSERV.CODPLA=PLAMANUT.CODPLA AND ORDSERV.CODEMP_1=PLAMANUT.CODEMP
      LEFT OUTER JOIN FILIAL ON FILIAL.CODFIL=ORDSERV.CODFIL AND FILIAL.CODEMP=ORDSERV.CODEMP_7  
      LEFT OUTER JOIN CLIENTE ON CLIENTE.CODCLI=ORDSERV.CODCLI

    WHERE ORDSERV.DATPRO2 BETWEEN '01/04/2016' AND '30/04/2016'
      AND ORDSERV.CODEMP=76)

      SELECT @EM_ABERTO AS EM_ABERTO,
      @FECHADAS AS FECHADAS,
      @REALIZADAS AS REALIZADAS,
      (@GERADAS - @REALIZADAS)  AS N_REALIZADAS,
      @CANCELADAS AS CANCELADAS,
      @GERADAS AS GERADAS,
      (@REALIZADAS)*100 / @GERADAS AS PORCENTAGEM_REALIZADAS

    EM_ABERTO  FECHADAS  REALIZADAS  N_REALIZADAS  CANCELADAS  GERADAS  PORCENTAGEM_REALIZADAS  
    ---------  --------  ----------  ------------  ----------  -------  ----------------------  
           22       707         659            48           0      707                      93 

    Mas quando coloco para consultar do '01/03/2016' and  '30/04/2016' ele soma os dois resultados do mês e com a porcentagem errada.

    EM_ABERTO  FECHADAS  REALIZADAS  N_REALIZADAS  CANCELADAS  GERADAS  PORCENTAGEM_REALIZADAS  
    ---------  --------  ----------  ------------  ----------  -------  ----------------------  
           41      1221        1148            73           0     1221                      94  

    quarta-feira, 11 de maio de 2016 18:24
  • Leonardo, no comando que te passei acima esqueci de tirar uma coisa.

    with tabela as (
    SELECT DATEPART(MM, ORDSERV.DATPRO2) MES, DATEPART(YY, ORDSERV.DATPRO2) Ano, 
    ENGEMAN.MESABREV(ORDSERV.DATPRO2)+'/'+SUBSTRING(CAST(DATEPART(YY, ORDSERV.DATPRO2) AS VARCHAR),3,2) MES_ANO,
    SUM(CASE WHEN ORDSERV.STATORD='F' THEN 1 ELSE 0 END) AS PORCENTAGEM
    FROM ORDSERV
    WHERE ORDSERV.DATPRO2 BETWEEN '01/03/2016' AND '30/04/2016'
    AND ORDSERV.STATORD='F'
    AND ORDSERV.CODEMP=76
    AND 'S' IN (SELECT REGSERV.EXECUTADO FROM REGSERV WHERE REGSERV.CODEMP=ORDSERV.CODEMP AND REGSERV.CODORD=ORDSERV.CODORD) 
    GROUP BY DATEPART(MM, ORDSERV.DATPRO2),ENGEMAN.MESABREV(ORDSERV.DATPRO2), DATEPART(YYYY, ORDSERV.DATPRO2)
    ORDER BY 1,2)
    
    
    select MES, Ano, MES_ANO, (PORCENTAGEM * 100)/count(*) as Porcentagem FROM tabela
    group by MES, Ano, MES_ANO

    Execute agora e veja se mudou alguma coisa.

    quarta-feira, 11 de maio de 2016 18:34
  • Boa tarde,

    Leonardo, experimente fazer uns testes dessa forma:

    SELECT DATEPART(MM, ORDSERV.DATPRO2) MES, DATEPART(YY, ORDSERV.DATPRO2) MES, 
    ENGEMAN.MESABREV(ORDSERV.DATPRO2)+'/'+SUBSTRING(CAST(DATEPART(YY, ORDSERV.DATPRO2) AS VARCHAR),3,2) MES_ANO,
    
    
    100.0 * SUM(CASE WHEN ORDSERV.STATORD = 'F' AND OA.EXECUTADO = 'S' THEN 1 ELSE 0 END) / COUNT(*) AS PORCENTAGEM
    
    FROM ORDSERV
    OUTER APPLY
    (
        SELECT REGSERV.EXECUTADO FROM REGSERV 
        WHERE REGSERV.CODEMP=ORDSERV.CODEMP AND REGSERV.CODORD=ORDSERV.CODORD
    ) AS OA
    
    
    WHERE ORDSERV.DATPRO2 BETWEEN '01/03/2016' AND '30/04/2016'
    AND ORDSERV.STATORD='F'
    AND ORDSERV.CODEMP=76 
    GROUP BY DATEPART(MM, ORDSERV.DATPRO2),ENGEMAN.MESABREV(ORDSERV.DATPRO2), DATEPART(YYYY, ORDSERV.DATPRO2)
    ORDER BY 1,2
    

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    • Marcado como Resposta Marcos SJ quinta-feira, 12 de maio de 2016 19:04
    quarta-feira, 11 de maio de 2016 18:37
  • Boa tarde Gapimex,

    Você chegou mais próximo do resultado, mas ainda não é o correto. 

    Os resultados agora deu 88% e 89% com sua opção o qual seria 95% e 93%. EX: Valores geradas 514 e Fechadas 489 daria uma diferença de 25 que seria mais ou menos 95%, no qual seria as fechadas *100 / pelas geradas.

    Vou tentar lhe explicar essa tabela REGSERV.EXECUTADO. Na tabela ORDSERV é vinculada a tabela REGSERV que possui o campo EXECUTADO que tem como opções 'S' para os serviços executados e 'N' para os serviços não executados. Exemplo: Na tabela ORDSERV existem vários serviços a serem executados sendo que de 10 serviços se o funcionário executar apenas 1 serviço 'S' e os outros 9 'N' não forem executados essa O.S. (ordem de serviço) entra no somatório das O.S. fechadas e executadas. Somente as O.S.s que tiverem todos os serviços 'N' não executados sairão da contagem mesmo fechadas. 

    Essa opção que você me passou ele conta a quantidade do registro em geral somando os serviços 'S' executados e 'N' não executados e dá o percentual no montante dos registros envolvendo tudo e não as O.S.s que tem o 'S' que entrará na contagem e as O.S.s 'N' não executadas que não entraram na porcentagem. 

    Espero ter entendido.

    quarta-feira, 11 de maio de 2016 19:58
  • Boa tarde,

    Não sei se entendi corretamente, mas experimente fazer uns testes alterando apenas o trecho do Outer Apply:

    OUTER APPLY
    (
        SELECT MAX(REGSERV.EXECUTADO) AS EXECUTADO FROM REGSERV 
        WHERE REGSERV.CODEMP=ORDSERV.CODEMP AND REGSERV.CODORD=ORDSERV.CODORD
    ) AS OA

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    • Marcado como Resposta Marcos SJ quinta-feira, 12 de maio de 2016 19:44
    quarta-feira, 11 de maio de 2016 20:13