Get ballance between In and out product/lot in a given date

Hi,

I have a table "EstLotes" where I keep some data referring to a certain lot. For example lot 205200073. Lots have a single input, but usually have multiple outputs. The Productin column is the quantity produced of a given product (Description) and has a single entry, the ProductOut column is the accumulated value of the outputs of that product/lot.
Then I have a join with the "Estlin" table, which are the various lot outputs, the Quant column (quantity) and the date it came out.
I wanted to get a single line that would return the lote, state, description fields, and another field with the difference between the input and the outputs.
In practice, I need to know what quantity there was on a given date for each lot.
If I for example make a query with the date of 2021-01-07 I should get a balance of 2991-1308=1683

select ESTLotes.lote,ESTLotes.State,ESTLotes.Description,ESTLotes.ProductIn,ESTLotes.ProductOut,
EstLin.Quant,EstLin.Date
from ESTLotes
inner join EstLin
on ESTLotes.lote=EstLin.lote
where EstLin.data <='2021-01-07' and EstLin.lote='205200073'

Select allOpen in new window

best regards