SyntaxTerror
Technical User
I am pulling from a few temp tables and am not sure how to join them so I get the right results. The goal is, in essence, to make the following calculation:
InventoryCount1+PurchaseOrdersMadeBetweenCount1and2-InventoryCount2=InventoryUsage
Tables:
1) Inventory Counts Temp Table containing CountIDs I need (Trust me, I need this temp table. It goes much deeper than this, but I'm trying to keep it simple)
2) Inventory Transactions Table containing inventory quantities for every individual item for every CountID (I need to join the this to the Inventory Counts Temp Table so I pull the quantities from the right counts)
3) Purchase Orders Temp Table (This contains the purchase order information I need for the time between counts 1 and 2, 2 and 3, 3 and 4, etc. Again, I need this table and am trying not to go too deep)
Basically, my worry is this... One count might contain items that another lacks, so I need to make sure to include all results from Count1 and Count2 (which sounds like a full outer join), but I need to join them to the InventoryCountsTempTable to know what information to pull from InventoryTransactions in the first place.
How can I join Count1 and Count2 to inventory transactions, but also to each other, then also join in the purchase order information without something getting lost or duplicated in the midst of all of the joins?
Sorry if this is confusing
I'm trying to make it understandable. It's much more confusing if you know the full story.
InventoryCount1+PurchaseOrdersMadeBetweenCount1and2-InventoryCount2=InventoryUsage
Tables:
1) Inventory Counts Temp Table containing CountIDs I need (Trust me, I need this temp table. It goes much deeper than this, but I'm trying to keep it simple)
2) Inventory Transactions Table containing inventory quantities for every individual item for every CountID (I need to join the this to the Inventory Counts Temp Table so I pull the quantities from the right counts)
3) Purchase Orders Temp Table (This contains the purchase order information I need for the time between counts 1 and 2, 2 and 3, 3 and 4, etc. Again, I need this table and am trying not to go too deep)
Basically, my worry is this... One count might contain items that another lacks, so I need to make sure to include all results from Count1 and Count2 (which sounds like a full outer join), but I need to join them to the InventoryCountsTempTable to know what information to pull from InventoryTransactions in the first place.
How can I join Count1 and Count2 to inventory transactions, but also to each other, then also join in the purchase order information without something getting lost or duplicated in the midst of all of the joins?
Sorry if this is confusing
I'm trying to make it understandable. It's much more confusing if you know the full story.
