Link to home
Create AccountLog in
Avatar of rp
rpFlag for Portugal

asked on

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


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 <='2021-01-07' and EstLin.lote='205200073'

Open in new window

User generated imageUser generated image
best regards

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of rp


Thanks. I just had to adjust 2 small details, it worked perfectly.