none
Exibir curva ABC RRS feed

  • Discussão Geral

  • Boa tarde!

    Estou precisando montar um relatório onde seja exibido a partir de uma % se o produto é A,B ou C em quantidade de vendas.

    Ex: Se o produto representa 75% em venda do total ele é A, se for 50% é B e se for 25% é C.

    O que tenho até agora é isso que vai mostrar a média de venda de cada produto:

    SELECT me.BARRA, me.DESCR_PROD, me.codprod,
           (sum (me.QTD) / count(distinct convert(char(6), me.DATA, 112))) as media
      from CAIXAGERAL me
      where me.DATA BETWEEN CONVERT(datetime, '01/01/2012', 103) AND CONVERT(datetime, '31/12/2013', 103)
      group by me.BARRA, me.DESCR_PROD, me.codprod

    • Tipo Alterado Giovani Cr quinta-feira, 12 de dezembro de 2013 12:09
    quinta-feira, 5 de dezembro de 2013 19:47

Todas as Respostas

  • Deleted
    quinta-feira, 5 de dezembro de 2013 20:52
  • Olá Diego.

    Curva ABC geralmente é um gráfico. O famoso 80-20, de Pareto.

    O que é para utilizar como valor de referência: a somatória da quantidade de vendas do produto no período OU a quantidade média mensal de vendas do produto no período?

    Sugestão de leitura prévia: Diagrama de Pareto

    No caso de vendas de produtos ao consumidor final, não faz sentido montar curva ABC para quantidades de unidades vendidas; geralmente se utiliza valores monetários, isto é, quanto as vendas daquele produto representou em termos monetários para a loja.

    O que é para utilizar como valor de referência?


        José Diz     Belo Horizonte, MG - Brasil


    Bom dia!!

    Preciso gerar sobre a quantidade média mensal de vendas do produto no período.

    Isto é pedido da diretoria pois irá montar um relatório dos produtos mais vendidos que serão o A para ser uma sugestão de compras

    sexta-feira, 6 de dezembro de 2013 11:05
  • Deleted
    sexta-feira, 6 de dezembro de 2013 12:41
  • Preciso gerar sobre a quantidade média mensal de vendas do produto no período.

    Ok.

    Diego, teve a oportunidade de ler o artigo sobre diagrama de Pareto, que consta em mensagem anterior? Conhecer o conceito é fundamental para entender o algoritmo da consulta.

    Segue um código didático, que mostra passo a passo as etapas para a criação da planilha. Não se levou em consideração a performance na execução da consulta, somente como demonstração.

    -- código 1d
    -- período de emissão do relatório
    declare @DataInicial datetime, @DataFinal datetime;
    set @DataInicial = convert(date, '1/1/2012', 103);
    set @DataFinal = convert(date, '31/12/2013', 103);
    
    -- ajusta data final para usar < na cláusula where
    set @DataFinal = DateAdd(day, +1, @DataFinal);
    
    --
    ;with 
    -- calcula a quantidade média para cada produto
    X1 as (
    SELECT CodProd, 
           (sum (Qtd) / count(distinct convert(char(6), Data, 112))) as QtdMedia
      from CAIXAGERAL
      where Data >= @DataInicial and Data < @DataFinal
      group by CodProd
    ),
    -- somatório dos valores de QtdMedia
    X2 as (
    SELECT sum(QtdMedia) as TotalQtdMedia
      from X1
    ),
    -- calcula o percentual de cada produto no todo
    X3 as (
    SELECT CodProd, QtdMedia, (QtdMedia / TotalQtdMedia * 100) as [% prod],
      Seq = row_number() over (order by QtdMedia desc)
      from X1 cross join X2
    ),
    -- calcula acumulado de percentual
    X4 as (
    SELECT CodProd, QtdMedia, [% prod],
           [% acum] = (SELECT sum([% prod]) from X3 as X3i where X3i.Seq <= X3.Seq)
      from X3
    )
    -- planilha para montar a curva ABC
    SELECT CodProd, 
           (SELECT Descr_Prod from CAIXAGERAL as CX where CX.CodProd=X3.CodProd) as Descr_Prod,
           QtdMedia, [% prod], [% acum],
           Classe = case when [% acum] >= 75 then 'A'
                         when [% acum] >= 50 then 'B'
                         else 'C' end
      from X4
      order by Seq;
     
    Acho que é isso; não tive como testar o código, pode conter erro.


        José Diz     Belo Horizonte, MG - Brasil
    (Se encontrou a solução nesta resposta, ou se o conteúdo foi útil, lembre-se de marcá-la)


    Li sim e irei ler mais sobre, muito obrigado.

    Deu erro no seu script, no (SELECT Descr_Prod from CAIXAGERAL as CX where CX.CodProd=X3.CodProd)  a expressão X3.CodProd não é reconhecida nem o order by Seq;

    sexta-feira, 6 de dezembro de 2013 16:09
  • Deleted
    sexta-feira, 6 de dezembro de 2013 17:17

  • -- código 1d ...
    (SELECT Descr_Prod from CAIXAGERAL as CX where CX.CodProd=X3.CodProd) as Descr_Prod,

    Deu erro no seu script, no (SELECT Descr_Prod from CAIXAGERAL as CX where CX.CodProd=X3.CodProd)  a expressão X3.CodProd não é reconhecida nem o order by Seq;

    :)

    Experimente a versão 1h acima.

    Após ter disponibilizado o código, usei o computador de baiano e encontrei alguns erros. Fiz modificações, inclusive duas que corrigem os erros que citou.

    Há um erro nas faixas que definem cada classe:
        "Se o produto representa 75% em venda do total ele é A, se for 50% é B e se for 25% é C."

    75% + 50% + 25% são 150%!
    Por favor verifique com o usuário como deve ser a segmentação das faixas. 

    No código 1h coloquei as seguintes faixas, como demonstração:
      A: até 75% de acumulado
      B: de 75% até 90% de acumulado; logo, uma faixa de 15%
      C: os 10% restantes.


        José Diz     Belo Horizonte, MG - Brasil


    Bom dia!!

    Eu havia acertado este erro e ia postar mas estou tentando ver uma forma de melhorar o desempenho pois está lento d+ para executar.

    As vezes pode ser a quantidade de dados que esta sendo gerado...

    segunda-feira, 9 de dezembro de 2013 12:17

  • -- código 1d ...
    (SELECT Descr_Prod from CAIXAGERAL as CX where CX.CodProd=X3.CodProd) as Descr_Prod,

    Deu erro no seu script, no (SELECT Descr_Prod from CAIXAGERAL as CX where CX.CodProd=X3.CodProd)  a expressão X3.CodProd não é reconhecida nem o order by Seq;

    :)

    Experimente a versão 1h acima.

    Após ter disponibilizado o código, usei o computador de baiano e encontrei alguns erros. Fiz modificações, inclusive duas que corrigem os erros que citou.

    Há um erro nas faixas que definem cada classe:
        "Se o produto representa 75% em venda do total ele é A, se for 50% é B e se for 25% é C."

    75% + 50% + 25% são 150%!
    Por favor verifique com o usuário como deve ser a segmentação das faixas. 

    No código 1h coloquei as seguintes faixas, como demonstração:
      A: até 75% de acumulado
      B: de 75% até 90% de acumulado; logo, uma faixa de 15%
      C: os 10% restantes.


        José Diz     Belo Horizonte, MG - Brasil


    Bom dia!!

    Eu havia acertado este erro e ia postar mas estou tentando ver uma forma de melhorar o desempenho pois está lento d+ para executar.

    As vezes pode ser a quantidade de dados que esta sendo gerado...

    Conversei aqui com os diretores e resolvemos mudar a forma desse relatórioa que ao invés de fazer o cálculo em cima da média fazer em cima da venda dentro do período...

    Tem como ajudar a mudar na query?

    segunda-feira, 9 de dezembro de 2013 12:43
  • Deleted
    segunda-feira, 9 de dezembro de 2013 14:26
  • ... estou tentando ver uma forma de melhorar o desempenho pois está lento d+ para executar.
    As vezes pode ser a quantidade de dados que esta sendo gerado...

    Diego, o código 1, como especificado anteriormente, é um código didático; ele mostra o passo a passo, sem se preocupar com a performance.  O que seria "está lento d+ para executar"? Poderia quantificar em unidade de tempo por quantidade de dados a serem lidos? Por exemplo, "5 minutos para processar o mês de janeiro de 2013".

    Para melhorar a performance, o código 1 pode ser ou otimizado ou reescrito de outra forma. Mas antes é necessário avaliar se o código está correto; testou-o com uma pequena massa de dados (somente um mês, por exemplo) para avaliar os resultados?

    Como não tenho acesso aos dados, não sei qual é o perfil dos mesmos; entretanto, a primeira questão a avaliar é: existe índice na tabela CAIXAGERAL cuja primeira (ou única) coluna seja a coluna DATA? Se não houver, e quiser fazer uma experiência, crie índice conforme o comando:

       create nonclustered index PorData on CAIXAGERAL (Data) include (CodProd, Qtd);

    e execute novamente o código 1. Pode ter certeza que esse índice vai dar uma turbinada na execução de X1. A existência das colunas incluídas (CodProd e Qtd) fará com que nem seja necessário acessar a tabela; tudo se resolve no próprio índice.

    Este índice, PorData, é somente um experimento. Para torná-lo permanente é necessário avaliar prós e contras da existência de um índice adicional na tabela CAIXAGERAL.

     
    Se analisar o plano de execução da consulta do código 1, perceberá que a CTE X1 é executada duas vezes: em X2 e em X3. Se não houver índice pela coluna DATA provavelmente está ocorrendo table/index scan na tabela CAIXAGERAL duas vezes... Isto não é bom, mas pode ser contornado. Se não for possível a criação do índice PorData tratado no parágrafo anterior, outra opção é transformar a CTE X1 em uma tabela temporária; deste modo X1 é executado uma única vez.

    PS: use a versão h do código 1 para os testes; fiz várias modificações após a versão d.


        José Diz     Belo Horizonte, MG - Brasil
    (Se encontrou a solução nesta resposta, ou se o conteúdo foi útil, lembre-se de marcá-la)


    A execução do mês demora cerca de 20 minutos cada mês.

    Olhei e só tem indice criado pra data, com isso inclui o seu pra ver se melhora. Uma pergunta, é automático a busca no indice contendo os critérios da pesquisa ou sempre tenho que chamar na query o indice?

    O indice é a forma de contornar a demora na execução?

    O resultado está batendo se é ABC corretamente...

    segunda-feira, 9 de dezembro de 2013 18:25
  • Deleted
    segunda-feira, 9 de dezembro de 2013 19:31
  • A execução do mês demora cerca de 20 minutos cada mês.

    Uau! Realmente está demorado...

    Olhei e só tem indice criado pra data, com isso inclui o seu pra ver se melhora.

    Se já havia um índice pela coluna data, e essa coluna é a primeira no índice, então é provável que esse índice já esteja em uso. Para confirmar, somente analisando o plano de execução gerado para a consulta. De qualquer forma, esse índice criado, PorData, é experimental. Após os testes ele deverá ser apagado, pois já existe um com data. 

    O índice que já existe é do tipo clustered ou nonclustered? Se for do tipo clustered, então a criação do índice PorData não irá melhorar a performance da consulta. 

      

    Uma pergunta, é automático a busca no indice contendo os critérios da pesquisa ou sempre tenho que chamar na query o indice?

    Existe um módulo do SQL Server que se chama otimizador de consulta. A princípio ele escolhe o índice mais adequado à consulta.

    É possível na consulta definir o índice a utilizar. Na CTE X1, basta substituir a linha
          from CAIXAGERAL
    por
          from CAIXAGERAL with (index (PorData))


    O indice é a forma de contornar a demora na execução?

    O ideal é primeiro analisar o plano de execução da consulta, para encontrar pontos de gargalo.
    Criar índice é uma das formas, mas é uma opção que deve ser avaliada tendo-se em mente todo o sistema. Um índice bem construído pode agilizar uma ou mais consultas mas, por outro lado, demanda tempo para ser mantido atualizado e também espaço.

    O resultado está batendo se é ABC corretamente...

    Ok. 


        José Diz     Belo Horizonte, MG - Brasil
    (Se encontrou a solução nesta resposta, ou se o conteúdo foi útil, lembre-se de marcá-la)


    Ele é do tipo nonclustered
    segunda-feira, 9 de dezembro de 2013 19:50
  • Deleted
    segunda-feira, 9 de dezembro de 2013 20:01
  • Ele é do tipo nonclustered

    Diego, se ele é do tipo nonclustered, e se a única coluna que há nele é a coluna DATA, sem qualquer coluna incluída, então o índice PorData será mais eficiente. Quando tiver a oportunidade de testar a consulta, já com o índice PorData criado e com a modificação na CTE X1 para que o índice PorData seja utilizado, por favor nos informe o tempo de resposta, ok?

        José Diz     Belo Horizonte, MG - Brasil
    (Se encontrou a solução nesta resposta, ou se o conteúdo foi útil, lembre-se de marcá-la)


    Não é a única coluna, dentro há mais colunas como qtde, barra, codprod...

    É interessante testar um indice somente com a DATA sem QTDE?

    segunda-feira, 9 de dezembro de 2013 20:05
  • Deleted
    terça-feira, 10 de dezembro de 2013 09:29
  • Qual o número aproximado de linhas que são produzidas neste relatório? Ou seja, qual o tamanho aproximado da tabela #X3, em número de linhas. É que, se ela for muito extensa, talvez compense criar o índice #IX3 para agilizar a execução da CTE X4.

        José Diz     Belo Horizonte, MG - Brasil
    (Se encontrou a solução nesta resposta, ou se o conteúdo foi útil, lembre-se de marcá-la)


    Bom dia!!

    A tabela CAIXAGERAL tem mais de 40 mil linhas rsrs

    A tabela temporária tanto na memória quanto no HD ficam ativas somente quando executa a query? Se sim, a query joga as informação da tabela CAIXAGERAL na memória para X1 e no HD para X3?

    No mês 2/2012 executou em menos de 1min, ou seja, o gargalo foi extinto! Porém tentei rodar em outro mês e deu a mensagem de There is already an object named '#X3' in the database.

    terça-feira, 10 de dezembro de 2013 10:51
  • Deleted
    terça-feira, 10 de dezembro de 2013 11:39
  • A tabela temporária tanto na memória quanto no HD ficam ativas somente quando executa a query? Se sim, a query joga as informação da tabela CAIXAGERAL na memória para X1 e no HD para X3?

    A tabela temporária #X3 é apagada ao final da execução de código 2. 

    Com relação às variáveis de memória, elas somente existem durante o processamento do lote ou da rotina. Vide DECLARE @local_variable 

    No mês 2/2012 executou em menos de 1min, ou seja, o gargalo foi extinto! Porém tentei rodar em outro mês e deu a mensagem de There is already an object named '#X3' in the database.

    De 20 minutos para 1 min? Com ou sem o índice PorData?

    Com relação à mensagem "There is already an object named '#X3' in the database", é estranho que tenha ocorrido pois no código 2 a última instrução é justamente apagar a tabela temporária:
           if OBJECT_ID('#X3','U') is not null drop table #X3;  

    Verifique se no código que executou se essa linha está presente.

    Mais estranho ainda é que a mesma linha está no início do código 2; ou seja, essa tabela não deveria existir antes do início de execução de X2.


        José Diz     Belo Horizonte, MG - Brasil
    (Se encontrou a solução nesta resposta, ou se o conteúdo foi útil, lembre-se de marcá-la)


    Está sem o indice que você pediu pra criar porém há o indice que o próprio banco já tinha e que contém a coluna DATA e mais colunas.

    Eu não relacionei na query o indice do sistema...

    O código if OBJECT_ID('#X3','U') is not null drop table #X3; está presente no início e fim da query conforme sua criação.

    terça-feira, 10 de dezembro de 2013 11:43
  • Deleted
    terça-feira, 10 de dezembro de 2013 12:04
  • código if OBJECT_ID('#X3','U') is not null drop table #X3; está presente no início e fim da query conforme sua criação.

    Estranho então...  Qual a versão do SQL Server?

    Execute o seguinte comando para apagar a tabela #X3 que permaneceu.
       drop table #X3;

    Experimente alterar a última linha de código 2 de
       if OBJECT_ID('#X3','U') is not null drop table #X3;
    para
       drop table #X3;


        José Diz     Belo Horizonte, MG - Brasil
    (Se encontrou a solução nesta resposta, ou se o conteúdo foi útil, lembre-se de marcá-la)


    Agora deu certo!

    Estou com sql server 2008 r2 x64

    Estou achando estranho pois só está aparecendo 10600 itens

    terça-feira, 10 de dezembro de 2013 12:56
  • Deleted
    terça-feira, 10 de dezembro de 2013 13:14
  • Estou achando estranho pois só está aparecendo 10600 itens

    Diego, quando possível altere a primeira ocorrência em código 2 de
       if OBJECT_ID('#X3','U') is not null drop table #X3;
    para
       if OBJECT_ID('tempdb..#X3','U') is not null drop table #X3;

    A última linha do código continua como fez:
        drop table #X3;

    Estão aparecendo 10.600 itens onde? No resultado da consulta? Se for, lembre-se de que logo no início do código é feito um agrupamento de linhas para cada produto. Estou até achando muito 10.600 itens ao final.

    Do resultado da consulta, poderia transcrever as dez primeiras linhas e as dez últimas linhas para este tópico?


        José Diz     Belo Horizonte, MG - Brasil
    (Se encontrou a solução nesta resposta, ou se o conteúdo foi útil, lembre-se de marcá-la)


    Eu achei até pouco pois tem muitos produtos

    10 primeiras:

    NULL    22451.00000    9.6748089478404163900563    9.6748089478404163900563    A
    1102    3158.00000    1.3608768721785236719878    11.0356858200189400620441    A
    5    2638.34600    1.1369423851186571297955    12.1726282051375971918396    A
    407999    2087.00000    0.8993508651794106723998    13.0719790703170078642394    A
    14052    1990.00000    0.8575506572625909142672    13.9295297275795987785066    A
    11    1927.14000    0.8304623988125776153371    14.7599921263921763938437    A
    415152    1732.00000    0.7463707228034208359350    15.5063628491955972297787    A
    648    1715.00000    0.7390449131685142803860    16.2454077623641115101647    A
    1165    1570.00000    0.6765600662825466007032    16.9219678286466581108679    A
    407999    1482.00000    0.6386382281724420778613    17.5606060568191001887292    A

    10 ultimas

    414111    0.73500    0.0003167335342150775487    99.9985537989920737411837    C
    408200    0.59400    0.0002559724072432055292    99.9988097713993169467129    C
    408281    0.59000    0.0002542486873291098691    99.9990640200866460565820    C
    417289    0.55000    0.0002370114881881532678    99.9993010315748342098498    C
    415760    0.52700    0.0002271000986821032220    99.9995281316735163130718    C
    414563    0.34700    0.0001495327025477985162    99.9996776643760641115880    C
    418863    0.29400    0.0001266934136860310195    99.9998043577897501426075    C
    418120    0.20400    0.0000879097156188786666    99.9998922675053690212741    C
    414589    0.16000    0.0000689487965638264052    99.9999612163019328476793    C
    414446    0.09000    0.0000387836980671523529    100.0000000000000000000322    C

    terça-feira, 10 de dezembro de 2013 13:47
  • Deleted
    terça-feira, 10 de dezembro de 2013 14:19
  • NULL    22451.00000    9.6748089478404163900563    9.6748089478404163900563    A

    1102    3158.00000    1.3608768721785236719878    11.0356858200189400620441    A
    ...
    414589    0.16000    0.0000689487965638264052    99.9999612163019328476793    C

    414446    0.09000    0.0000387836980671523529    100.0000000000000000000322    C

    Diego, duas observações:
     (1) não faz sentido produto(s) com código NULL... Há algo de errado ou no código 2 ou nos dados; ou em ambos. Qual a diferença entre as colunas BARRA e CodProd da tabela CAIXAGERAL? O que consta na coluna de descrição do produto para a primeira linha do relatório?

     (2) Com relação às últimas linhas, penso que o valor da coluna QtdMedia deveria ser sempre igual ou maior do que 1, mas estão aparecendo valores com valor médio de quantidade vendida inferior a 1. Ou existem produtos cuja unidade de venda seja fracionária? Na tabela CAIXAGERAL há linhas cujo valor da coluna Qtd seja inferior a 1?

     
    O que contém a tabela CAIXAGERAL?

    Porque a linha de CAIXAGERAL contém a descrição do produto? Não há outra tabela somente com as informações de produtos, com informações específicas do produto, inclusive a descrição do produto?

    No tópico Media quando há dado constam duas linhas com o mesmo código de barra (07506195129876) mas com descrições diferentes; como isso é possível? O código de produto dessas duas linhas é o mesmo? 

    Observe que as linhas 4 e 10 possuem o mesmo código de produto! Isto somente é possível se possuem descrições diferentes (por causa do agrupamento CodProd, DescrProd que havia em código 2 v1). O que identifica um produto, de forma única? 


        José Diz     Belo Horizonte, MG - Brasil


    Foi vacilo meu, acrescentei ((TIPOLANCTO='') OR (CODPROD IS NOT NULL)) para não pegar os nulos que são causas do início do sistema (eu não estava aqui) e esqueci de
    tirar a descrição do produto do group by pois há 2 descrições pro produto. Uma normal e outra pra promoção por isso códigos iguais com médias diferentes.
    Coluna barra é código de barra e cod é o código do produto.
    Há produtos fracionados sim.

    Segue nova consulta com as 10 primeiras linhas:

    407999    3569.00000    1.7027244287407250300806    1.7027244287407250300806    A
    5    3325.33600    1.5864754387702346900051    3.2891998675109597200857    A
    1102    3158.00000    1.5066415651339898136718    4.7958414326449495337575    A
    415152    3036.00000    1.4484369194891681679251    6.2442783521341177016826    A
    11    2231.78000    1.0647538037475414142990    7.3090321558816591159816    A
    415479    2007.39000    0.9577001936144141266835    8.2667323494960732426651    A
    14052    1990.00000    0.9494036461737301232447    9.2161359956698033659098    A
    7125    1881.00000    0.8974011349008976692579    10.1135371305707010351677    A
    33    1842.28500    0.8789307016538544724130    10.9924678322245555075807    A
    648    1715.00000    0.8182046498431895283240    11.8106724820677450359047    A

    10 últimas linhas:

    4651    414111    0.73500    0.0003506591356470812264    99.9983988951575080209730    C
    4652    408200    0.59400    0.0002833898320739676850    99.9986822849895819886580    C
    4653    408281    0.59000    0.0002814814830364325491    99.9989637664726184212071    C
    4654    417289    0.55000    0.0002623979926610811898    99.9992261644652795023969    C
    4655    415760    0.52700    0.0002514249856952541583    99.9994775894509747565552    C
    4656    414563    0.34700    0.0001655492790061730416    99.9996431387299809295968    C
    4657    418863    0.29400    0.0001402636542588324906    99.9997834023842397620874    C
    4658    418120    0.20400    0.0000973258009142919322    99.9998807281851540540196    C
    4659    414589    0.16000    0.0000763339615014054370    99.9999570621466554594566    C
    4660    414446    0.09000    0.0000429378533445405583    100.0000000000000000000149    C

    terça-feira, 10 de dezembro de 2013 19:02
  • NULL    22451.00000    9.6748089478404163900563    9.6748089478404163900563    A

    1102    3158.00000    1.3608768721785236719878    11.0356858200189400620441    A
    ...
    414589    0.16000    0.0000689487965638264052    99.9999612163019328476793    C

    414446    0.09000    0.0000387836980671523529    100.0000000000000000000322    C

    Diego, duas observações:
     (1) não faz sentido produto(s) com código NULL... Há algo de errado ou no código 2 ou nos dados; ou em ambos. Qual a diferença entre as colunas BARRA e CodProd da tabela CAIXAGERAL? O que consta na coluna de descrição do produto para a primeira linha do relatório?

     (2) Com relação às últimas linhas, penso que o valor da coluna QtdMedia deveria ser sempre igual ou maior do que 1, mas estão aparecendo valores com valor médio de quantidade vendida inferior a 1. Ou existem produtos cuja unidade de venda seja fracionária? Na tabela CAIXAGERAL há linhas cujo valor da coluna Qtd seja inferior a 1?

     
    O que contém a tabela CAIXAGERAL?

    Porque a linha de CAIXAGERAL contém a descrição do produto? Não há outra tabela somente com as informações de produtos, com informações específicas do produto, inclusive a descrição do produto?

    No tópico Media quando há dado constam duas linhas com o mesmo código de barra (07506195129876) mas com descrições diferentes; como isso é possível? O código de produto dessas duas linhas é o mesmo? 

    Observe que as linhas 4 e 10 possuem o mesmo código de produto! Isto somente é possível se possuem descrições diferentes (por causa do agrupamento CodProd, DescrProd que havia em código 2 v1). O que identifica um produto, de forma única? 


        José Diz     Belo Horizonte, MG - Brasil


    Foi vacilo meu, acrescentei ((TIPOLANCTO='') OR (CODPROD IS NOT NULL)) para não pegar os nulos que são causas do início do sistema (eu não estava aqui) e esqueci de
    tirar a descrição do produto do group by pois há 2 descrições pro produto. Uma normal e outra pra promoção por isso códigos iguais com médias diferentes.
    Coluna barra é código de barra e cod é o código do produto.
    Há produtos fracionados sim.

    Segue nova consulta com as 10 primeiras linhas:

    407999    3569.00000    1.7027244287407250300806    1.7027244287407250300806    A
    5    3325.33600    1.5864754387702346900051    3.2891998675109597200857    A
    1102    3158.00000    1.5066415651339898136718    4.7958414326449495337575    A
    415152    3036.00000    1.4484369194891681679251    6.2442783521341177016826    A
    11    2231.78000    1.0647538037475414142990    7.3090321558816591159816    A
    415479    2007.39000    0.9577001936144141266835    8.2667323494960732426651    A
    14052    1990.00000    0.9494036461737301232447    9.2161359956698033659098    A
    7125    1881.00000    0.8974011349008976692579    10.1135371305707010351677    A
    33    1842.28500    0.8789307016538544724130    10.9924678322245555075807    A
    648    1715.00000    0.8182046498431895283240    11.8106724820677450359047    A

    10 últimas linhas:

    4651    414111    0.73500    0.0003506591356470812264    99.9983988951575080209730    C
    4652    408200    0.59400    0.0002833898320739676850    99.9986822849895819886580    C
    4653    408281    0.59000    0.0002814814830364325491    99.9989637664726184212071    C
    4654    417289    0.55000    0.0002623979926610811898    99.9992261644652795023969    C
    4655    415760    0.52700    0.0002514249856952541583    99.9994775894509747565552    C
    4656    414563    0.34700    0.0001655492790061730416    99.9996431387299809295968    C
    4657    418863    0.29400    0.0001402636542588324906    99.9997834023842397620874    C
    4658    418120    0.20400    0.0000973258009142919322    99.9998807281851540540196    C
    4659    414589    0.16000    0.0000763339615014054370    99.9999570621466554594566    C
    4660    414446    0.09000    0.0000429378533445405583    100.0000000000000000000149    C

    Só que ele não tras todos os produtos que serial quase 37 mil e sim menos da metade, 11

    Agora eu tenho que juntar em outra query pra ficar tudo numa só porém duplica a quantidade de itens, que raiva rsrs

    Ajuda a encontrar o erro onde não o vejo:

    set NOCOUNT ON;
    if OBJECT_ID('tempdb..#X3','U') is not null drop table #X3;

    -- calcula a quantidade média para cada produto
    declare @X1 table (CodProd numeric(6,0), QtdMedia numeric(12,5), Seq int identity, Data datetime, qtd numeric(12,3));

    INSERT into @X1 (CodProd, data, QtdMedia, qtd)
       SELECT CodProd, data, qtd,
           (sum (Qtd) / count(distinct convert(char(6), Data, 112))) as QtdMedia
         from CAIXAGERAL
         where DATA BETWEEN CONVERT(datetime, '01/01/2012', 103) AND CONVERT(datetime, '31/12/2013', 103)
         --and ((TIPOLANCTO='') OR (CODPROD IS NOT NULL))
         group by CodProd,data,qtd;

        
         with

        CTE_CX as
        (
            select
                        CX.CODPROD,
                        [01/2012] = SUM(case when YEAR (cx.data)=2012 and MONTH(cx.data)=01 then cx.qtd end),
                        [02/2012] = SUM(case when YEAR (cx.data)=2012 and MONTH(cx.data)=02 then cx.qtd end),
                        [03/2012] = SUM(case when YEAR (cx.data)=2012 and MONTH(cx.data)=03 then cx.qtd end),
                        [04/2012] = SUM(case when YEAR (cx.data)=2012 and MONTH(cx.data)=04 then cx.qtd end),
                        [05/2012] = SUM(case when YEAR (cx.data)=2012 and MONTH(cx.data)=05 then cx.qtd end),
                        [06/2012] = SUM(case when YEAR (cx.data)=2012 and MONTH(cx.data)=06 then cx.qtd end),
                        [07/2012] = SUM(case when YEAR (cx.data)=2012 and MONTH(cx.data)=07 then cx.qtd end),
                        [08/2012] = SUM(case when YEAR (cx.data)=2012 and MONTH(cx.data)=08 then cx.qtd end),
                        [09/2012] = SUM(case when YEAR (cx.data)=2012 and MONTH(cx.data)=09 then cx.qtd end),
                        [10/2012] = SUM(case when YEAR (cx.data)=2012 and MONTH(cx.data)=10 then cx.qtd end),
                        [11/2012] = SUM(case when YEAR (cx.data)=2012 and MONTH(cx.data)=11 then cx.qtd end),
                        [12/2012] = SUM(case when YEAR (cx.data)=2012 and MONTH(cx.data)=12 then cx.qtd end),
                        [01/2013] = SUM(case when YEAR (cx.data)=2013 and MONTH(cx.data)=01 then cx.qtd end),
                        [02/2013] = SUM(case when YEAR (cx.data)=2013 and MONTH(cx.data)=02 then cx.qtd end),
                        [03/2013] = SUM(case when YEAR (cx.data)=2013 and MONTH(cx.data)=03 then cx.qtd end),
                        [04/2013] = SUM(case when YEAR (cx.data)=2013 and MONTH(cx.data)=04 then cx.qtd end),                   
                        [05/2013] = SUM(case when YEAR (cx.data)=2013 and MONTH(cx.data)=05 then cx.qtd end),
                        [06/2013] = SUM(case when YEAR (cx.data)=2013 and MONTH(cx.data)=06 then cx.qtd end),
                        [07/2013] = SUM(case when YEAR (cx.data)=2013 and MONTH(cx.data)=07 then cx.qtd end),
                        [08/2013] = SUM(case when YEAR (cx.data)=2013 and MONTH(cx.data)=08 then cx.qtd end),
                        [09/2013] = SUM(case when YEAR (cx.data)=2013 and MONTH(cx.data)=09 then cx.qtd end),
                        [10/2013] = SUM(case when YEAR (cx.data)=2013 and MONTH(cx.data)=10 then cx.qtd end),
                        [11/2013] = SUM(case when YEAR (cx.data)=2013 and MONTH(cx.data)=11 then cx.qtd end),
                        [12/2013] = SUM(case when YEAR (cx.data)=2013 and MONTH(cx.data)=12 then cx.qtd end)
            from @X1 CX
                  group by CX.CODPROD
        ),
    CTE_ME as
        (
    SELECT me.BARRA, me.codprod,
           (sum (me.QTD) / count(distinct convert(char(6), me.DATA, 112))) as media
      from CAIXAGERAL me
      where me.DATA BETWEEN CONVERT(datetime, '01/01/2012', 103) AND CONVERT(datetime, '31/12/2013', 103)
      group by me.BARRA, me.codprod
        ),

        CTE_ITM as
        (
            select
                ITM.CODPROD,
                sum(ITM.QTDNOTA) as ENTRADA
            from PRODUTOS P,ENTRADANF NF
            inner join ITMENTRADANF ITM
                on ITM.CODENTRADANF = NF.CODENTRADANF
            where
                NF.DTENTRADA BETWEEN CONVERT(datetime, '01/12/2013', 103) AND CONVERT(datetime, '31/12/2013', 103) and
                ( (NF.SERIE = '999' AND NF.MODELO='55') OR (NF.SERIE<>'999') ) and
                ITM.CODPROD = P.CODPROD
            group by
                ITM.CODPROD
        )
    SELECT
         cr.DESCRICAO as "CENTRO RECEITA",
        g.descricao AS GRUPO,
        c.DESCRICAO as CATEGORIA,
        p.BARRA AS BARRA,
        p.DESCRICAO AS DESCRICAO,
    cx.[01/2012],
    cx.[02/2012],
    cx.[03/2012],
    cx.[04/2012],
    cx.[05/2012],
    cx.[06/2012],
    cx.[07/2012],
    cx.[08/2012],
    cx.[09/2012],
    cx.[10/2012],
    cx.[11/2012],
    cx.[12/2012],
    cx.[01/2013],
    cx.[02/2013],
    cx.[03/2013],
    cx.[04/2013],
    cx.[05/2013],
    cx.[06/2013],
    cx.[07/2013],
    cx.[08/2013],
    cx.[09/2013],
    cx.[10/2013],
    cx.[11/2013],
    cx.[12/2013],
    me.media as MEDIA,
        p.preco_cust as "PREÇO CUSTO",
        p.PRECO_UNIT AS "PREÇO",
        P.ESTOQUE,
        ITM.ENTRADA
    from produtos p
    inner join grupo g
        on g.codgrupo = p.codgrupo
    inner join CRECEITA cr
        on cr.codcreceita=p.codcreceita
    inner join CATEGORIA c
        on c.CODCATEGORIA=p.CODCATEGORIA
    left join CTE_CX CX
        on cx.CODPROD = p.CODPROD
    inner join CTE_ME ME
        on me.CODPROD = p.CODPROD    
    left join CTE_ITM ITM
        on ITM.CODPROD = p.CODPROD
    order by p.BARRA

    terça-feira, 10 de dezembro de 2013 20:23
  • Deleted
    terça-feira, 10 de dezembro de 2013 21:42
  • Coluna barra é código de barra e cod é o código do produto.

    Qual coluna é suficiente para identificar o produto, de forma confiável?  A coluna CodProd, pelo que comentou anteriormente, não é confiável, pois há linhas que estão com o valor NULL nessa coluna. 

    Há produtos fracionados sim.

    Ok. De qualquer forma, avalie com atenção toda linha do relatório que apresente quantidade média menor do que 1. 

    Só que ele não traz todos os produtos que serial quase 37 mil e sim menos da metade, 11
      

    Mas isso faz sentido, pois estão sendo agrupadas várias linhas de um mesmo produto em uma única. Por exemplo, considerando-se somente as colunas CodProd, Data e Qtd, se temos em CAIXAGERAL as linhas
        18  -  2/1/2012  -  22,000
        18  - 17/3/2012 -  35,000
        18  -  4/4/2012  -  2,000
    na tabela @X1 teremos as três linhas de CAIXAGERAL sintetizadas em uma única:  (colunas CodProd e QtdMedia)
        18  -  19,66666

    Agora eu tenho que juntar em outra query pra ficar tudo numa só

    No código que transcreveu foram realizadas alterações no trecho que gera a tabela @X1 e que fazem com que o conteúdo dela fique errado. No código 2 v5 consta o seguinte código para carregar a tabela @X1:

    -- calcula a quantidade média para cada produto
    declare @X1 table (CodProd int, QtdMedia numeric(12,5), Seq int identity);
    
    INSERT into @X1 (CodProd, QtdMedia)
       SELECT CodProd,
           (sum (Qtd) / count(distinct convert(char(6), Data, 112))) as QtdMedia
         from CAIXAGERAL
         where Data >= @DataInicial and Data < @DataFinal
               and Qtd > 0
         group by CodProd
         order by QtdMedia desc;  

    Já no código que transcreveu consta 

    -- calcula a quantidade média para cada produto
    declare @X1 table (CodProd numeric(6,0), QtdMedia numeric(12,5), Seq int identity, Data datetime, qtd numeric(12,3));
    
    INSERT into @X1 (CodProd, data, QtdMedia, qtd)
       SELECT CodProd, data, qtd,
           (sum (Qtd) / count(distinct convert(char(6), Data, 112))) as QtdMedia
         from CAIXAGERAL
         where DATA BETWEEN CONVERT(datetime, '01/01/2012', 103) AND CONVERT(datetime, '31/12/2013', 103)
         --and ((TIPOLANCTO='') OR (CODPROD IS NOT NULL))
         group by CodProd,data,qtd;

    Para que a tabela @X1 seja montada corretamente é fundamental que:
      1) exista o trecho order by QtdMedia desc.  Sem ela, o valor da coluna Seq é gerado errado, ocasionando que a coluna [% acum] possa conter valor errado.
      2) na cláusula group by somente conste(m) coluna(s) que identifique(m) o produto de forma única. 

    Ao acrescentar as colunas Data e Qtd no código que monta a tabela @X1, o cálculo de QtdMedia ficará errado. É necessário que, para calcular QtdMedia corretamente, as linhas sejam agrupadas somente pela(s) coluna(s) que identifique(m) o produto de forma única.  Data e quantidade não são identificações mas sim valores temporais.

    Considerando-se que a tabela @X1 é uma síntese de um período, conforme explicado há pouco, então nela não podem constar valores individuais de data e de quantidade. 


        José Diz     Belo Horizonte, MG - Brasil
    (Se encontrou a solução nesta resposta, ou se o conteúdo foi útil, lembre-se de marcá-la)


    Bom dia!!

    A coluna confiável é a de codprod e barra pois ambas não são duplicadas. Mesmo havendo algumas codprod null é erro na implantação do sistema mas tem como evitar o erro colocando a cláusula CX.CODPROD IS NOT NULL

    Sobre a quantidade de produto não faz sentido pois hoje tenho 36 mil itens então teriam que aparecer na query os 36 ao inves de 11, mesmo estando sem venda...

    Eu preciso também na query geral que seja exibido a qtd de vendas de todos os produtos dentro do período, por isso o CASE. Preciso que seja exibido a MEDIA de vendas de cada produto porém fazendo o calculo somente quando teve venda e por fim se ele é curva ABC.

    Com isso estou tentando criar uma query, a partir de sua ajuda e de outros neste mesmo fórum, contendo tudo isso. Se puder ajudar nesse processo final agradeço muito pois estou aprendendo muita coisa que nunca vi antes e fico contente que há pessoas que ajudam.

    quarta-feira, 11 de dezembro de 2013 11:45
  • As médias não batem e com isso a curva também não.

    Na sua query o resultado da a média de 34,79 classe B:

    -- período de emissão do relatório
    declare @DataInicial datetime, @DataFinal datetime;
    --declare @X1 table (CodProd numeric(6,0));
    set @DataInicial = convert(date, '1/1/2012', 103);
    set @DataFinal = convert(date, '31/12/2013', 103);

    -- ajusta data final para usar < na cláusula where
    set @DataFinal = DateAdd(day, +1, @DataFinal);

    --
    set NOCOUNT ON;
    if OBJECT_ID('tempdb..#X3','U') is not null drop table #X3;

    -- calcula a quantidade média para cada produto
    declare @X1 table (CodProd numeric(6,0), QtdMedia numeric(12,5), Seq int identity);

    INSERT into @X1 (CodProd, QtdMedia)
       SELECT CodProd,
           (sum (Qtd) / count(distinct convert(char(6), Data, 112))) as QtdMedia
         from CAIXAGERAL --with (index (PorData))
         where Data >= @DataInicial and Data < @DataFinal
         and CODPROD IS NOT NULL
         group by CodProd
         order by QtdMedia desc;  

    --
    with X2 as (
    -- somatório dos valores de QtdMedia
    SELECT Seq, CodProd, QtdMedia, sum(QtdMedia) over () as TotalQtdMedia
      from @X1
    )
    -- calcula o percentual de cada produto no todo
    SELECT Seq, CodProd, QtdMedia, (QtdMedia / TotalQtdMedia * 100) as [% prod]
      into #X3
      from X2;
    --create clustered index #IX3 on #X3 (Seq);
     
    with X4 as (
    SELECT Seq, CodProd,QtdMedia, [% prod],
           [% acum]= (SELECT sum([% prod]) from #X3 as X3i where X3i.Seq <= X3.Seq)
      from #X3 as X3
    )
    -- planilha para montar a curva ABC
    SELECT CodProd, QtdMedia, [% prod], [% acum],
           Classe= case when [% acum] < 75 then 'A'  
                        when [% acum] < 90 then 'B'
                        else 'C' end
      from X4
      WHERE CodProd=416002
      order by Seq;

    drop table #X3; 

    Na minha query da 40,27 classe A:

    -- período de emissão do relatório
    declare @DataInicial datetime, @DataFinal datetime;
    --declare @X1 table (CodProd numeric(6,0));
    set @DataInicial = convert(date, '1/1/2012', 103);
    set @DataFinal = convert(date, '31/12/2013', 103);

    -- ajusta data final para usar < na cláusula where
    set @DataFinal = DateAdd(day, +1, @DataFinal);

    --
    set NOCOUNT ON;
    if OBJECT_ID('tempdb..#X3','U') is not null drop table #X3;

    -- calcula a quantidade média para cada produto
    declare @X1 table (CodProd numeric(6,0), QtdMedia numeric(12,5), Seq int identity);

    INSERT into @X1 (CodProd, QtdMedia)
        
            select
                        CX.CODPROD,
                        (sum (cx.Qtd) / count(distinct convert(char(6), Data, 112))) as QtdMedia                    
            from CAIXAGERAL CX
            where CX.CODPROD IS NOT NULL
            group by CodProd
            order by QtdMedia desc;
     
    -- calcula a quantidade de vendas por mês para cada produto
    declare @X2 table (CodProd numeric(6,0), data datetime, qtd numeric (12,3));

    insert into @X2 (codprod, data, qtd)
            select
                        cxc.codprod, DATA, qtd
                            from CAIXAGERAL CXC
            where CXC.CODPROD IS NOT NULL
            group by CXC.CodProd, data, qtd;
     
    with X2 as (
    -- somatório dos valores de QtdMedia
    SELECT Seq, CodProd, QtdMedia, sum(QtdMedia) over () as TotalQtdMedia
        from @X1
    )
    -- calcula o percentual de cada produto no todo
    SELECT Seq, CodProd, QtdMedia, (QtdMedia / TotalQtdMedia * 100) as [% prod]
      into #X3
      from X2;
    --create clustered index #IX3 on #X3 (Seq);
     
    with X4 as (
    SELECT Seq, CodProd,QtdMedia, [% prod],
           [% acum]= (SELECT sum([% prod]) from #X3 as X3i where X3i.Seq <= X3.Seq)
      from #X3 as X3
    ),

        CTE_CXC as
        (
            select
                        CODPROD,
                        [01/2012] = SUM(case when YEAR (data)=2012 and MONTH(data)=01 then qtd end),
                        [02/2012] = SUM(case when YEAR (data)=2012 and MONTH(data)=02 then qtd end),
                        [03/2012] = SUM(case when YEAR (data)=2012 and MONTH(data)=03 then qtd end),
                        [04/2012] = SUM(case when YEAR (data)=2012 and MONTH(data)=04 then qtd end),
                        [05/2012] = SUM(case when YEAR (data)=2012 and MONTH(data)=05 then qtd end),
                        [06/2012] = SUM(case when YEAR (data)=2012 and MONTH(data)=06 then qtd end),
                        [07/2012] = SUM(case when YEAR (data)=2012 and MONTH(data)=07 then qtd end),
                        [08/2012] = SUM(case when YEAR (data)=2012 and MONTH(data)=08 then qtd end),
                        [09/2012] = SUM(case when YEAR (data)=2012 and MONTH(data)=09 then qtd end),
                        [10/2012] = SUM(case when YEAR (data)=2012 and MONTH(data)=10 then qtd end),
                        [11/2012] = SUM(case when YEAR (data)=2012 and MONTH(data)=11 then qtd end),
                        [12/2012] = SUM(case when YEAR (data)=2012 and MONTH(data)=12 then qtd end),
                        [01/2013] = SUM(case when YEAR (data)=2013 and MONTH(data)=01 then qtd end),
                        [02/2013] = SUM(case when YEAR (data)=2013 and MONTH(data)=02 then qtd end),
                        [03/2013] = SUM(case when YEAR (data)=2013 and MONTH(data)=03 then qtd end),
                        [04/2013] = SUM(case when YEAR (data)=2013 and MONTH(data)=04 then qtd end),                   
                        [05/2013] = SUM(case when YEAR (data)=2013 and MONTH(data)=05 then qtd end),
                        [06/2013] = SUM(case when YEAR (data)=2013 and MONTH(data)=06 then qtd end),
                        [07/2013] = SUM(case when YEAR (data)=2013 and MONTH(data)=07 then qtd end),
                        [08/2013] = SUM(case when YEAR (data)=2013 and MONTH(data)=08 then qtd end),
                        [09/2013] = SUM(case when YEAR (data)=2013 and MONTH(data)=09 then qtd end),
                        [10/2013] = SUM(case when YEAR (data)=2013 and MONTH(data)=10 then qtd end),
                        [11/2013] = SUM(case when YEAR (data)=2013 and MONTH(data)=11 then qtd end),
                        [12/2013] = SUM(case when YEAR (data)=2013 and MONTH(data)=12 then qtd end)
            from @X2
            group by CodProd
            ),
        CTE_ITM as
        (
            select
                ITM.CODPROD,
                sum(ITM.QTDNOTA) as ENTRADA
            from PRODUTOS P,ENTRADANF NF
            inner join ITMENTRADANF ITM
                on ITM.CODENTRADANF = NF.CODENTRADANF
            where
                NF.DTENTRADA BETWEEN CONVERT(datetime, '01/12/2013', 103) AND CONVERT(datetime, '31/12/2013', 103) and
                ( (NF.SERIE = '999' AND NF.MODELO='55') OR (NF.SERIE<>'999') ) and
                ITM.CODPROD = P.CODPROD
            group by
                ITM.CODPROD
        )
    SELECT
         cr.DESCRICAO as "CENTRO RECEITA",
        g.descricao AS GRUPO,
        c.DESCRICAO as CATEGORIA,
        p.BARRA AS BARRA,
        p.CODPROD as COD,
        p.DESCRICAO AS DESCRICAO,
    cxc.[01/2012],
    cxc.[02/2012],
    cxc.[03/2012],
    cxc.[04/2012],
    cxc.[05/2012],
    cxc.[06/2012],
    cxc.[07/2012],
    cxc.[08/2012],
    cxc.[09/2012],
    cxc.[10/2012],
    cxc.[11/2012],
    cxc.[12/2012],
    cxc.[01/2013],
    cxc.[02/2013],
    cxc.[03/2013],
    cxc.[04/2013],
    cxc.[05/2013],
    cxc.[06/2013],
    cxc.[07/2013],
    cxc.[08/2013],
    cxc.[09/2013],
    cxc.[10/2013],
    cxc.[11/2013],
    cxc.[12/2013],
        p.preco_cust as "PREÇO CUSTO",
        p.PRECO_UNIT AS "PREÇO",
        x4.QtdMedia as MEDIA,
        P.ESTOQUE,
        ITM.ENTRADA,
        --X4.QtdMedia, X4.[% prod], X4.[% acum],
           Classe= case when [% acum] < 75 then 'A'  
                        when [% acum] < 90 then 'B'
                        else 'C' end
    from produtos p
    inner join grupo g
        on g.codgrupo = p.codgrupo
    inner join X4
        on x4.CODPROD=p.CODPROD    
    inner join CRECEITA cr
        on cr.codcreceita=p.codcreceita
    inner join CATEGORIA c
        on c.CODCATEGORIA=p.CODCATEGORIA
    inner join CTE_CXC cxc
        on cxc.CODPROD = p.CODPROD    
    inner join @X1 CX
        on cx.CODPROD = p.CODPROD
     
    left join CTE_ITM ITM
        on ITM.CODPROD = p.CODPROD
    where p.codprod=416002
    order by p.descricao
    drop table #X3;

          

    quarta-feira, 11 de dezembro de 2013 14:19
  • Bom dia!!

    Vamos acertar sua fórmula primeiro pois a média está com valor errado. Pegar 1 produto e o mesmo teve as seguintes vendas por mês:

    8.000    11.000    15.000    17.000    19.000    17.000    7.000    20.000    16.000    16.000    10.000      19.000    57.000    15.000    9.000    8.000    1.000    1.000    7.000 ou seja a média é 14,368 e a query resulta em 34.89474

    quinta-feira, 12 de dezembro de 2013 12:08