none
If dentro de um Select RRS feed

  • Pergunta

  • Estou precisando montar uma planilha para curva ABC, onde necessito testar se os percentuais são de tanto a tanto para classificar entre A, B e C, e criar uma coluna com essa informação. Como no exemplo abaixo:

    Segue tbém o código utilizado para geração da planilha:

    ;with cte_dados as (
    SELECT DISTINCT         
            P.CODIGOPRD, 
            P.NOMEFANTASIA AS DESCRICAO,       
            L.SALDOFISICO2 AS SALDO_FISICO, 
            L.CUSTOMEDIO, 
            L.SALDOFISICO2 * L.CUSTOMEDIO AS SALDO_FINANCEIRO,
            (SELECT SUM(L.SALDOFISICO2 * L.CUSTOMEDIO) FROM TPRD P, TPRDLOC L WHERE P.INATIVO = 0 AND P.TIPO = 'P' AND L.SALDOFISICO2 > 0 AND P.IDPRD = L.IDPRD AND L.CODLOC = '007' ) AS TOTAL
    FROM TPRD P, TPRDLOC L 
    WHERE P.INATIVO = 0 
           AND P.TIPO = 'P' 
           AND L.SALDOFISICO2 > 0 
           AND P.IDPRD = L.IDPRD 
           AND L.CODLOC = '007' 
    ), cte_dados2 as (select row_number() over(order by CODIGOPRD) as numerador, CODIGOPRD, DESCRICAO, SALDO_FISICO, CUSTOMEDIO, SALDO_FINANCEIRO, TOTAL from cte_dados)
    
    select *,
        isnull((select a.SALDO_FINANCEIRO + sum(b.SALDO_FINANCEIRO) from cte_dados2 b where numerador <= a.numerador-1),a.SALDO_FINANCEIRO) as SaldoAcumulado,
        (isnull((select a.SALDO_FINANCEIRO + sum(b.SALDO_FINANCEIRO) from cte_dados2 b where numerador <= a.numerador-1),a.SALDO_FINANCEIRO))/TOTAL  
    from cte_dados2 a
    

    Grata,

    Rafaela Alves


    Rafaela Alves

    quinta-feira, 27 de junho de 2013 16:03

Respostas

  • Boa tarde,

    Rafaela, para esse caso você pode utilizar um Case.

    Não testei, mas tomei também a liberdade de sugerir algumas alterações na sua query.

    ;with 
        cte_dados as 
        (
            SELECT DISTINCT         
                P.CODIGOPRD, 
                P.NOMEFANTASIA AS DESCRICAO,       
                L.SALDOFISICO2 AS SALDO_FISICO, 
                L.CUSTOMEDIO, 
                L.SALDOFISICO2 * L.CUSTOMEDIO AS SALDO_FINANCEIRO,
                SUM(L.SALDOFISICO2 * L.CUSTOMEDIO) OVER() AS TOTAL
            FROM TPRD P
            INNER JOIN TPRDLOC L 
                ON L.IDPRD = P.IDPRD
            WHERE 
                P.INATIVO = 0 AND 
                P.TIPO = 'P' AND 
                L.SALDOFISICO2 > 0 AND 
                L.CODLOC = '007' 
        ),
    
        cte_dados2 as
        (
            select
                *,
                a.SALDO_FINANCEIRO + isnull((select sum(b.SALDO_FINANCEIRO) from cte_dados b where numerador < a.numerador), 0) as SaldoAcumulado,
                row_number() over(order by SALDO_FINANCEIRO DESC) as numerador
            from cte_dados a
        )
    
    select 
        *,
        SaldoAcumulado / TOTAL as [% valor],
        case
            when SaldoAcumulado / TOTAL <= 80 then 'A'
            when SaldoAcumulado / TOTAL <= 95 then 'B'
            else 'C'
        end as Classe 
    from cte_dados2 

    Espero que seja útil.

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

    • Marcado como Resposta Rafaela Alves segunda-feira, 1 de julho de 2013 19:50
    quinta-feira, 27 de junho de 2013 17:36
  • Deleted
    • Marcado como Resposta Rafaela Alves segunda-feira, 1 de julho de 2013 19:49
    sexta-feira, 28 de junho de 2013 00:39
  • Deleted
    • Marcado como Resposta Rafaela Alves terça-feira, 2 de julho de 2013 12:02
    segunda-feira, 1 de julho de 2013 21:36

Todas as Respostas

  • Boa tarde,

    Rafaela, para esse caso você pode utilizar um Case.

    Não testei, mas tomei também a liberdade de sugerir algumas alterações na sua query.

    ;with 
        cte_dados as 
        (
            SELECT DISTINCT         
                P.CODIGOPRD, 
                P.NOMEFANTASIA AS DESCRICAO,       
                L.SALDOFISICO2 AS SALDO_FISICO, 
                L.CUSTOMEDIO, 
                L.SALDOFISICO2 * L.CUSTOMEDIO AS SALDO_FINANCEIRO,
                SUM(L.SALDOFISICO2 * L.CUSTOMEDIO) OVER() AS TOTAL
            FROM TPRD P
            INNER JOIN TPRDLOC L 
                ON L.IDPRD = P.IDPRD
            WHERE 
                P.INATIVO = 0 AND 
                P.TIPO = 'P' AND 
                L.SALDOFISICO2 > 0 AND 
                L.CODLOC = '007' 
        ),
    
        cte_dados2 as
        (
            select
                *,
                a.SALDO_FINANCEIRO + isnull((select sum(b.SALDO_FINANCEIRO) from cte_dados b where numerador < a.numerador), 0) as SaldoAcumulado,
                row_number() over(order by SALDO_FINANCEIRO DESC) as numerador
            from cte_dados a
        )
    
    select 
        *,
        SaldoAcumulado / TOTAL as [% valor],
        case
            when SaldoAcumulado / TOTAL <= 80 then 'A'
            when SaldoAcumulado / TOTAL <= 95 then 'B'
            else 'C'
        end as Classe 
    from cte_dados2 

    Espero que seja útil.

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

    • Marcado como Resposta Rafaela Alves segunda-feira, 1 de julho de 2013 19:50
    quinta-feira, 27 de junho de 2013 17:36
  • Deleted
    • Marcado como Resposta Rafaela Alves segunda-feira, 1 de julho de 2013 19:49
    sexta-feira, 28 de junho de 2013 00:39
  • Obrigada pelo auxilio, deu certin com as sugestões de vcs, só fiz algumas adaptações, e  a única coisa estranha que acontece é que quando retiro o DISTINCT dá erro de TIME OUT na consulta, então deixei ele quetinho ali mesmo hehehe..

    O Código ficou assim:

    ;with 
    cte_dados as (
    SELECT DISTINCT P.CODIGOPRD, 
           P.NOMEFANTASIA AS DESCRICAO,       
           L.SALDOFISICO2 AS SALDO_FISICO, 
           L.CUSTOMEDIO, 
           L.SALDOFISICO2 * L.CUSTOMEDIO AS SALDO_FINANCEIRO,
           SUM(L.SALDOFISICO2 * L.CUSTOMEDIO) OVER() AS TOTAL
      FROM TPRD P
           INNER JOIN TPRDLOC L  ON L.IDPRD = P.IDPRD
      WHERE P.INATIVO = 0 AND 
           P.TIPO = 'P' AND 
           L.SALDOFISICO2 > 0 AND 
           L.CODLOC = '007' 
    ),
    cte_dados2 as (
    select *, row_number() over(order by SALDO_FINANCEIRO desc) as numerador
      from cte_dados
    ),
    cte_dados3 as (
    select *, isnull((select a.SALDO_FINANCEIRO + sum(b.SALDO_FINANCEIRO) from cte_dados2 b where numerador <= a.numerador-1),a.SALDO_FINANCEIRO) as SaldoAcumulado
      from cte_dados2 a
    )
    select CODIGOPRD, DESCRICAO, SALDO_FISICO, CUSTOMEDIO, SALDO_FINANCEIRO, 
           SaldoAcumulado, (SaldoAcumulado / TOTAL) as [% valor],
          case
            when (SaldoAcumulado / TOTAL) <= 0.8 then 'A'
            when (SaldoAcumulado / TOTAL) BETWEEN 81 AND 95 then 'B'
            else 'C'
          end as Classe 
      from cte_dados3;

    Atenciosamente,

    Rafaela Alves


    Rafaela Alves

    segunda-feira, 1 de julho de 2013 19:52
  • Deleted
    • Marcado como Resposta Rafaela Alves terça-feira, 2 de julho de 2013 12:02
    segunda-feira, 1 de julho de 2013 21:36
  • Eu modifiquei a parte abaixo, pois a questão de percentual já tratei direto no excel por questão de "estética".

    SaldoAcumulado, ((SaldoAcumulado / TOTAL) * 100) as [% valor]

    Modifiquei também a  parte abaixo, pois na curta ABC é <=0.80 = 'A'  >0.80 e <= 0.95 = 'B' .

    case
            when (SaldoAcumulado / TOTAL) <= 0.8 then 'A'
            when (SaldoAcumulado / TOTAL) <= 0.95 then 'B'
            else 'C'

    Ficando dessa forma:

    case when (SaldoAcumulado / TOTAL) <= 0.8 then 'A' when (SaldoAcumulado / TOTAL) BETWEEN 0.81 AND 0.95 then 'B'

    Obrigada.


    Rafaela Alves

    terça-feira, 2 de julho de 2013 12:01
  • Rafaela,

    Acredito que seja melhor manter o Case da forma como o José Diz sugeriu, pois a condição do segundo When é verificada apenas se a condição do primeiro When não é verdadeira, ou seja, se o resultado da divisão for  maior que 0.80.

    Outro detalhe é alguns percentuais da classe B não atendem a condição BETWEEN 0.81 AND 0.95 , como por exemplo 0.805.

    Espero que ajude.


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

    terça-feira, 2 de julho de 2013 13:49