We are creating a warehouse system. In this system, we will have a Extract page (I don't know if this is the right word to describe it, but it's the same as the log in our bank account, where we have all transactions, with the values and then, subtract it to the total value, it's a bank extract I guess).
We need a table to be like this:
The table will have all transactions from fiscal notes (with entries and exits of products). If you notice, you can see the
ID 1 will appear in position 1, 2, and 4. I did try to use
LAG, but with the
LAG function, it's getting the
ID 3 value.
How can I get the lag value, but with the desired product id? I need the last
total value of PR_ID = 1.
I tried this:
begin tran insert into almoxarifado.Movimentacao (produto_id,documento,data,saldo_anterior,entradas,saidas) select Lotes.produto_id as produto_id, NF.numero as documento, nf.data_cadastro as data, >>The lag value would be here, I need this field, to be the last total value, >>but from this specific product. Lotes.quantidade as entradas, 0 as saidas from Almoxarifado.Entradas join Compras.Notas_Fiscais NF on Entradas.nota_fiscal_id = NF.id join Compras.Notas_Fiscais_Produtos NFP on NFP.nota_fiscal_id = NF.id join Almoxarifado.Lotes on Lotes.nota_fiscal_produto_id = NFP.id where convert(date,NF.data_cadastro) = '2017-01-10'
Every row needs to have its own total. Let's say day 2, we had 3 water gallons, it gives me a total of 10. then, day 4 I got 3 gallons, I need the row with the total = 10, and in this new row, the total of 13.
It's like a bank extract, where you see your expenses, and the total on each row. Because, the next step would create a view, that will select this table, by product, so , I can see every total, every entry and product exit during a period of time.