Hello guys this is my first post here. I have a query which is to show everything on the sales order that has been allocated. Problem is since i have to use the oibt and ibt1 table (batches table) it displays duplicate and it shows every batch number for that allocation.
basically i just want to see what i see on the sales order and two colums displaying total needed and total allocated.
Problem is i tried to do
inv1.quantity-oibt.quantity as 'Total needed', however since for example i have two batches, the first time it subtracts the total allocated from the total needed, however on the second batch it looks back on the total quantity displaying wrong data.
Example
ITEM | Sales ORDER # | QTY NEEDED | QTY Allocated | Total Needed|
1 34895 50 35 15
1 34895 50 5 45
On the example above the first calculation was correct, however the 2nd time it went back to the 50 qty,
I want to figure how to get the 2nd row to display 10 on the total needed. I was thinking of using grouping on the bachnum but i don't know how to integrate it into this query.