none
Controle de Estoque

    Pergunta

  • Pessoal! Boa tarde!

    Estou aqui lascado com um problema, preciso montar um controle de estoque, que me retorne sempre o valor médio de cada produto até o momento de sua venda, e que sempre altere, aumente/diminua este valor de acordo com o último valor de compra.

    Tenho quatro tabelas que fazem isso, e que montei de uma forma que eu conseguisse pelo menos relacionar os itens.

    O problema agora é conseguir fazer com que esse cálculo aconteça, mesmo que nem seja necessário manter as colunas de acumulado:

    Vide Script:

    SELECT
    ELC.DataCompra as DataMovimento,
    ELI.Id_Produto,
    ELI.Quantidade,
    ELI.ValorTotal,
    CAST(ELI.ValorTotal / ELI.Quantidade AS numeric(18,2)) AS ValorUnitario,
    'Entrada' as Tipo,
    0 as QtideAcum,
    0 as ValorAcum,
    0 as UnitAcum
    FROM
    Ele_ComprasItens ELI
    LEFT JOIN Ele_Compras ELC ON (ELI.Id_Compra = ELC.Id_Compra)
    -- ORDER BY
    -- ELC.DataCompra,
    -- ELI.Id_Produto

    UNION

    SELECT
    ELE.DataVenda as DataMovimento,
    ELV.Id_Produto,
    ELV.Quantidade,
    0 AS ValorTotal,
    0 AS ValorUnitario,
    'Saída' as Tipo,
    0 as QtideAcum,
    0 as ValorAcum,
    0 as UnitAcum
    FROM
    Ele_VendaItens ELV
    LEFT JOIN Ele_Vendas ELE ON (ELE.Id_Venda = ELV.Id_Venda)
    --ORDER BY
    -- ELE.DataVenda,
    -- ELV.Id_Produto

    A tabela fica assim:

    Se alguém tiver alguma ideia para me ajudar agradeço.

    Obrigado.


    Robson Giovanni Parisoto.

    quinta-feira, 11 de janeiro de 2018 21:28

Respostas

  • Robson,

    Um detalhe importante, se você deseja manter o histórico, além de ter o valores alterados ao longo do tempo, de alguma forma você deverá controlar e identificar quem é o valor atual a ser utilizado.

    Tive uma necessidade similar a sua a muito tempo, e para justamente manter este controle e também a posição atual criar uma coluna que era responsável em versionar as alterações de valores e definir uma hierarquia histórica ao longo do tempo, sabendo que o valor que eu deveria utilizar era justamente com base na coluna neste coluna e seu maior valor hierárquico atribuído.

    Outra forma seria utilizar ao invés de uma coluna para gerar um número hierárquico uma coluna para guardar a data da alteração do valor.


    Pedro Antonio Galvao Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 12 de janeiro de 2018 23:30
  • Robson,

    Pode ser uma possibilidade, isso vai depender muito das suas regras de negócio, agora como você esta trabalhando em ambiente de cloud, uma consideração importante a ser analisada é a latência de rede.


    Pedro Antonio Galvao Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 12 de janeiro de 2018 23:25

Todas as Respostas

  • Robson,

    Talvez seja o caso de implementar esta rotina de análise e atualização do cálculo do preço médio via Job em seu servidor SQL Server, ou então desenvolver uma aplicação que rode em segundo plano em seu servidor como um serviço do Windows.

    Qual solução você acredita que possa ser mais útil?


    Pedro Antonio Galvao Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quinta-feira, 11 de janeiro de 2018 22:23
  • Boa noite Junior!

    Tenho tudo hospedado no Azure, faz uns dois meses, e estou aprendendo a mexer lá, inclusive pra ver se consigo criar um ETL que resolveria isso.

    Você acha que o caminho seria este mesmo? Eu startar um job toda vez que se efetua uma compra ou se lança uma saída?


    Robson Giovanni Parisoto.

    sexta-feira, 12 de janeiro de 2018 00:49
  • Vou te dar uma ideia simples, prática e eficiente. Implemente triggers. dessa forma você pode ter seu estóque atualizado em tempo real e também o preço médio.
    sexta-feira, 12 de janeiro de 2018 12:08
  • Vou te dar uma ideia simples, prática e eficiente. Implemente triggers. dessa forma você pode ter seu estóque atualizado em tempo real e também o preço médio.

    Bom dia J. Souza!

    Obrigado pela dica.

    Será que consigo fazer rodar no Azure? 


    Robson Giovanni Parisoto.

    sexta-feira, 12 de janeiro de 2018 12:15
  • Bem, estou instalando uma VM no meu Azure pra fazer alguns testes, vamos ver como se comporta.

    Robson Giovanni Parisoto.

    sexta-feira, 12 de janeiro de 2018 13:37
  • Boa tarde, Robson Giovanni Parisoto.

    Ficamos no aguardo de um retorno então.

    Atenciosamente,


    Filipe B de Castro

    Esse conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita

    MSDN Community Support

    Por favor, lembre-se de Marcar como Resposta as postagens que resolveram o seu problema. Essa é uma maneira comum de reconhecer aqueles que o ajudaram e fazer com que seja mais fácil para os outros visitantes encontrarem a resolução mais tarde.

    sexta-feira, 12 de janeiro de 2018 17:07
    Moderador
  • (...) preciso montar um controle de estoque, que me retorne sempre o valor médio de cada produto até o momento de sua venda, e que sempre altere, aumente/diminua este valor de acordo com o último valor de compra.

    Me parece que o valor médio de aquisição pode ficar armazenado na tabela de produtos ou então em uma tabela específica, com relacionamento 1:1 com a tabela de produtos. Sempre que for registrada uma nova compra, basta recalcular o valor médio, que pode ser realizado lendo todas as aquisições anteriores para o produto (mais simples de implementar, mas requer mais recursos computacionais para calcular) ou então utilizar acumulações intermediárias (requer colunas adicionais para implementar, mas consome menos recursos computacionais).

    Segue sugestão que utliza colunas adicionais na tabela de produtos.

    -- código #1 v2
    ALTER TABLE Produto 
      add AcumAq int default 0,
          ValorMédio numeric (10, 2) default 0.00;

    UPDATE Produto
    set AcumAq= 0,
    ValorMédio= 0.00;

    A coluna AcumAq registra a quantidade de itens adquiridos, desde o início da acumulação. Ou seja, a cada aquisição a quantidade de itens adquiridos deve ser acrescentada nessa coluna. Finalizando, a coluna ValorMédio contém o valor médio de aquisição.

    Cada vez que for registrada uma nova nota fiscal de compra, a aplicação executa o seguinte código:

    -- código #2 v3
    UPDATE P
      set AcumAq= AcumAq + ELI.Quantidade,
          ValorMédio= ((ValorMédio * AcumAq) + ELI.ValorTotal) /
                        (AcumAq + ELI.Quantidade)
      from Produto as P
           inner join Ele_ComprasItens as ELI on P.ID = ELI.Id_Produto
      where ELI.Id_Compra = ...
    and ...
     
    É preciso ficar atento que, se houver correções nos itens de uma nota fiscal, o valor médio de aquisição também deve ser recalculado.


    e-mail       José Diz     Belo Horizonte, MG - Brasil



    • Editado José Diz sexta-feira, 12 de janeiro de 2018 19:32
    sexta-feira, 12 de janeiro de 2018 19:08
  • Boa tarde José! Obrigado pelo apoio!

    Tem possibilidade de mudar os valores do histórico com este código ou ela não modificaria?


    Robson Giovanni Parisoto.

    sexta-feira, 12 de janeiro de 2018 19:42
  • Tem possibilidade de mudar os valores do histórico com este código ou ela não modificaria?

    A forma sugerida anteriormente registra somente o valor médio atual de cada item de produto. Se o objetivo é manter histórico de valor médio, nesse caso as colunas podem ser acrescentadas na tabela de movimentação, mas teriam informação registrada somente para as aquisições (movimentações de entrada). É este o objetivo?


    e-mail       José Diz     Belo Horizonte, MG - Brasil



    • Editado José Diz sexta-feira, 12 de janeiro de 2018 22:42
    sexta-feira, 12 de janeiro de 2018 19:58
  • Tem possibilidade de mudar os valores do histórico com este código ou ela não modificaria?

    A forma sugerida anteriormente registra somente o valor médio atual de cada item de produto. Se o objetivo é manter histórico de valor médio, nesse caso as colunas podem ser acrescentadas na tabela de movimentação, mas teriam informação registrada somente para as aquisições (movimentações de entrada). É este o objetivo?

    e-mail       José Diz     Belo Horizonte, MG - Brasil


    Preciso manter histórico José!

    Pois terei uma conexão direta dos orçamentos lá.

    E também vou precisar acrescentar as quantidades das duas tabelas, pois aí vou tendo a redução do estoque, e quando uma compra for executada, o valor médio vai se atualizar sem influência do que já foi vendido.

    Não sei se consegui te explicar direito, qualquer coisa me avise.

    Obrigado mais uma vez.

    Robson.


    Robson Giovanni Parisoto.

    sexta-feira, 12 de janeiro de 2018 20:57
  • Preciso manter histórico José!

    Na tabela de movimentação de entrada consta a data de  movimentação; o que ocorre se há mais de uma aquisição para um mesmo produto no mesmo dia? Isto é, como diferenciar a ordem dessas aquisições?

    No código que transcreveu no início deste tópico percebe-se que optou por acumular os valores de quantidade e de valor total; é um caminho. Neste caso é necessário ficar atento aos valores que a coluna de acumulação de valor total pode assumir, para não ocorrer estouro. E também é necessário ficar atento ao espaço ocupado pelo tipo de dados numeric, que varia de acordo com o número de algarismos permitidos.

    Uma forma de manter o histórico de valor médio é calculá-lo no momento de inclusão de linha na tabela Ele_ComprasItens. Para isto, eis sugestão de modificação na tabela Ele_ComprasItens:

    -- código #3 v4
    -- altera a estrutura da tabela de compras
    ALTER TABLE Ele_ComprasItens
      drop column ValorAcum, UnitAcum, QtideAcum;
     
    ALTER TABLE Ele_ComprasItens
      add ValorUnitario as cast(ValorTotal / Quantidade as numeric(9,2)),
          ValorAcum numeric(19,2) not null default 0,
          QtideAcum int check (QtideAcum > 0),
          ValorMedio as cast((ValorAcum / QtideAcum) as numeric (9,2));

    -- atualiza acumulações para as linhas já existentes
    UPDATE Ele_ComprasItens
      set ValorAcum= (SELECT sum(T.ValorTotal)
                         from Ele_ComprasItens as T
                         where T.Id_Produto = Ele_ComprasItens.Id_Produto
                               and T.DataCompra <= Ele_ComprasItens.DataCompra),
          QtideAcum= (SELECT sum(T.Quantidade)
                         from Ele_ComprasItens as T
                         where T.Id_Produto = Ele_ComprasItens.Id_Produto
                               and T.DataCompra <= Ele_ComprasItens.DataCompra);

    O código #3 é para ser executado uma única vez.

     
    E o código de inclusão de cada produto adquirido passa a ser o seguinte:

    -- código #4 v3
    declare @ValorAcum numeric(19,2), @QtideAcum int;
    
    -- procura a aquisição imediatamente anterior para o produto
    SELECT top (1) @ValorAcum= ValorAcum, @QtideAcum= QtideAcum from Ele_ComprasItens where Id_Produto = @Id_Produto and DataCompra < @DataCompra order by DataCompra desc; set @ValorAcum= coalesce(@ValorAcum, 0);
    set @QtideAcum= coalesce(@QtideAcum, 0);
    -- insere a movimentação de entrada do produto
    INSERT into Ele_ComprasItens (DataCompra, Id_Produto, Quantidade, ValorTotal, QtideAcum, ValorAcum) values (@DataCompra, @Id_Produto, @Quantidade, @ValorTotal, (@QtideAcum + @Quantidade), (@ValorAcum + @ValorTotal) );

    O código acima assume que existem as variáveis @DataCompra, @Id_Produto, @Quantidade, @ValorTotal, com os respectivos valores para a inclusão.

    http://sqlfiddle.com/#!6/229b6b/1


    e-mail       José Diz     Belo Horizonte, MG - Brasil



    sexta-feira, 12 de janeiro de 2018 22:42
  • Robson,

    Pode ser uma possibilidade, isso vai depender muito das suas regras de negócio, agora como você esta trabalhando em ambiente de cloud, uma consideração importante a ser analisada é a latência de rede.


    Pedro Antonio Galvao Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 12 de janeiro de 2018 23:25
  • Robson,

    Um detalhe importante, se você deseja manter o histórico, além de ter o valores alterados ao longo do tempo, de alguma forma você deverá controlar e identificar quem é o valor atual a ser utilizado.

    Tive uma necessidade similar a sua a muito tempo, e para justamente manter este controle e também a posição atual criar uma coluna que era responsável em versionar as alterações de valores e definir uma hierarquia histórica ao longo do tempo, sabendo que o valor que eu deveria utilizar era justamente com base na coluna neste coluna e seu maior valor hierárquico atribuído.

    Outra forma seria utilizar ao invés de uma coluna para gerar um número hierárquico uma coluna para guardar a data da alteração do valor.


    Pedro Antonio Galvao Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 12 de janeiro de 2018 23:30