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

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'

Open in new window


User generated imageUser generated image
best regards

ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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

ASKER

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