Is it possible to use LAG() with WHERE?

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.

Let me know if this solve your problem.

I've set up a rextester example to test it.

I've used this data:

create table #mov(produto_id int, documento int, data_cadastro datetime, entradas int, salidas int);

insert into #mov values
(1, 1, '2017-01-01', 10, 0),
(1, 1, '2017-01-02', 10, 2),
(2, 1, '2017-01-01', 10, 0),
(1, 1, '2017-01-03', 10, 0),
(3, 1, '2017-01-02', 10, 0);

To get the cumulative sum, apply SUM() OVER clause in this way:

select 
    produto_id, 
    documento,
    entradas,
    salidas,
    sum(entradas-salidas) over (partition by produto_id order by produto_id, data_cadastro
    rows between unbounded preceding and current row) as acumulato
from
    #mov

This is the result:

+------------+-----------+----------+---------+-----------+
| produto_id | documento | entradas | salidas | acumulato |
+------------+-----------+----------+---------+-----------+
|      1     |     1     |    10    |    0    |     10    |
+------------+-----------+----------+---------+-----------+
|      1     |     1     |     0    |    2    |     8     |
+------------+-----------+----------+---------+-----------+
|      1     |     1     |    10    |    0    |     18    |
+------------+-----------+----------+---------+-----------+
|      2     |     1     |    12    |    0    |     12    |
+------------+-----------+----------+---------+-----------+
|      3     |     1     |     5    |    0    |     5     |
+------------+-----------+----------+---------+-----------+

If you need the last acumulato in the same row, you can use:

;with cteSum as
(
    select 
        produto_id,
        data_cadastro,
        documento,
        entradas,
        salidas,
        sum(entradas-salidas) over (partition by produto_id order by produto_id, data_cadastro
        rows between unbounded preceding and current row) as acumulato
    from
        #mov
)
select produto_id, documento, entradas, salidas, acumulado, 
       lag(acumulato,1,0) over (partition by produto_id order by produto_id, data_cadastro) as last_acm
from cteSum;

+------------+-----------+----------+---------+-----------+----------+
| produto_id | documento | entradas | salidas | acumulato | last_acm |
+------------+-----------+----------+---------+-----------+----------+
|      1     |     1     |    10    |    0    |     10    |     0    |
+------------+-----------+----------+---------+-----------+----------+
|      1     |     1     |     0    |    2    |     8     |    10    |
+------------+-----------+----------+---------+-----------+----------+
|      1     |     1     |    10    |    0    |     18    |     8    |
+------------+-----------+----------+---------+-----------+----------+
|      2     |     1     |    12    |    0    |     12    |     0    |
+------------+-----------+----------+---------+-----------+----------+
|      3     |     1     |     5    |    0    |     5     |     0    |
+------------+-----------+----------+---------+-----------+----------+

StackExchange Database Administrators Q#160736, answer score: 5