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



    sexta-feira, 12 de janeiro de 2018 19:08
    Moderador
  • 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



    sexta-feira, 12 de janeiro de 2018 19:58
    Moderador
  • 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.

    Falta avaliar o funcionamento do código #4 acima considerando-se a concorrência de processos.

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


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



    sexta-feira, 12 de janeiro de 2018 22:42
    Moderador
  • 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
  • Pessoal!

    Enfim, depois de terminar outras demandas que tornaram-se urgentes, voltei pra cá para finalizar este processo.

    Para melhorar, criei uma tabela de chamada Ele_Estoque, e vou tentar incluir nas tabelas de Compras e Vendas as quantidades movimentadas conforme já falamos.

    Vou aproveitar a ajuda que me deram (que é extremamente valiosa), e se ficar com mais dúvidas aviso.

    Obrigado.


    Robson Giovanni Parisoto.

    sábado, 10 de fevereiro de 2018 21:38
  • Fala gurizada!

    Consegui terminar agora e iniciei meus testes, aí deu este erro:

    "Cannot have any enabled triggers if the statement contains an output clause without into clause".

    Será que é por que estou executando através do PowerApps?

    Abs.


    Robson Giovanni Parisoto.

    terça-feira, 13 de fevereiro de 2018 20:49
  • Robson,

    Você esta utilizando a cláusula Output no código fonte do seu Trigger?

    Se sim é necessário adicionar a instrução Insert para que o retorno do Output seja apresentado.


    Pedro Antonio Galvão 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]

    quarta-feira, 14 de fevereiro de 2018 11:12
  • Robson,

    Você esta utilizando a cláusula Output no código fonte do seu Trigger?

    Se sim é necessário adicionar a instrução Insert para que o retorno do Output seja apresentado.


    Pedro Antonio Galvão 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]

    Bom dia Junior! Obrigado mais uma vez pelo apoio que tem me dado.

    Então, é minha primeira trigger, o que fiz foi o seguinte (adaptei do código do José incluindo as entradas, para depois fazer as saídas):

    CREATE TRIGGER Custo_Estoque
    ON Ele_ComprasItens
    FOR INSERT
    AS
    BEGIN
    	DECLARE
    		@VALORACUM NUMERIC (18, 3),
    		@QTIDEACUM INT,
    		@DATACOMPRA DATETIME,
    		@IDCOMPRA INT,
    		@IDPRODUTO INT,
    		@QUANTIDADE INT,
    		@VALORUNIT NUMERIC (18, 3),
    		@VALORTOTAL NUMERIC (18, 3);
    
    	SELECT TOP (1)
    		@VALORACUM = ValorTotal,
    		@QTIDEACUM = Quantidade
    	FROM
    		ELE_ESTOQUE
    	WHERE
    		Produto = @IDPRODUTO
    		AND (SELECT TOP 1 DATACOMPRA FROM Ele_Compras WHERE Id_Compra = @IDCOMPRA) = @DATACOMPRA
    	ORDER BY
    		Data DESC;
    
    	SET @VALORACUM = COALESCE(@VALORACUM, 0);
    	SET @QTIDEACUM = COALESCE(@QTIDEACUM, 0);
    
    	INSERT INTO
    		Ele_Estoque
    		(Data, Produto, Quantidade, ValorUnitario, ValorTotal)
    	VALUES
    		(@DATACOMPRA, @IDPRODUTO, (@QTIDEACUM + @QUANTIDADE), ((@VALORACUM + @VALORTOTAL) / (@QTIDEACUM + @QUANTIDADE)), (@VALORACUM + @VALORTOTAL));
    END

    Se tiver algo errado, por favor, não hesite em me avisar.

    Obrigado.

    Robson.


    Robson Giovanni Parisoto.

    quarta-feira, 14 de fevereiro de 2018 12:00
  • Robson,

    Aparentemente o código esta correto em sua sintaxe, por acaso existe algum outro trigger que você esta utilizando?


    Pedro Antonio Galvão 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]

    quarta-feira, 14 de fevereiro de 2018 12:11
  • Buenas Junior!

    Olha, nenhum outro, o que acontece é que está no PowerApps, ligado ao Azure.

    Será que isso pode estar influenciando em algo?

    Abs.

    Robson.


    Robson Giovanni Parisoto.

    quarta-feira, 14 de fevereiro de 2018 12:28
  • Robson,

    Pode ser que sim, pois o PowerApps é um ambiente totalmente diferente para se trabalhar com aplicações móveis, mas não possui todo conjunto de recursos e funcionalidades existentes por exemplo em um ambiente Visual Studio.

    Você já tentou realizar algum outro teste em outro ambiente?


    Pedro Antonio Galvão 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, 16 de fevereiro de 2018 11:30
  • Então, é minha primeira trigger, o que fiz foi o seguinte (adaptei do código do José incluindo as entradas, para depois fazer as saídas):

    Robson, o código que postei é para utilizar no aplicativo. Ele não funciona para procedimentos de gatilho.


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



    sexta-feira, 16 de fevereiro de 2018 14:18
    Moderador
  • Robson,

    Pode ser que sim, pois o PowerApps é um ambiente totalmente diferente para se trabalhar com aplicações móveis, mas não possui todo conjunto de recursos e funcionalidades existentes por exemplo em um ambiente Visual Studio.

    Você já tentou realizar algum outro teste em outro ambiente?


    Pedro Antonio Galvão 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]

    Não tentei Pedro! Vou dar mais uma estudada para ver se consigo ajustar isso de alguma forma, nem que eu instale uma VM no Azure que faça isso de lá, através de um ETL talvez.

    Vou olhar e retorno com mais ideias.

    Obrigado mais uma vez.


    Robson Giovanni Parisoto.

    quarta-feira, 21 de fevereiro de 2018 19:37
  • Então, é minha primeira trigger, o que fiz foi o seguinte (adaptei do código do José incluindo as entradas, para depois fazer as saídas):

    Robson, o código que postei é para utilizar no aplicativo. Ele não funciona para procedimentos de gatilho.


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



    Entendi José!

    Realmente não sabia, provavelmente não me atentei a algo ou simplesmente a burrice é maior que a força de vontade, kkk...

    Vou tentar mais algumas alternativas, depois voltamos a conversar.

    Obrigado.

    Robson.


    Robson Giovanni Parisoto.

    quarta-feira, 21 de fevereiro de 2018 19:38
  • Bom dia pessoal!

    O problema está mesmo no PowerApps, pois consigo iniciar a Trigger através do SSMS ao fazer um insert (por exemplo).

    Vou ver se consigo encontrar alguma alternativa dentro do Azure para solucionar isso, provavelmente exista algo lá que possa ser feito, talvez através de uma VM com Integration Services, etc., vamos ver.

    Se alguém tiver alguma ideia por favor me avise.

    Obrigado.

    Robson.


    Robson Giovanni Parisoto.

    terça-feira, 27 de fevereiro de 2018 11:46