June 29, 2011 at 9:16 am
Hi all and thanks for the assistance ...
I'm trying to (logically) combine to tables for a total inventory count, then subtract items from a third table.
I UNION two tables as such:
/* Inventory Purchased Afloat */
SELECTunitQty AS qtyIn, POLineID
FROM dbo.tblPOLine AS po
WHERE (locationID = 6)
UNION
/* Inventory Purchased At Orgin Shipped */
SELECT tr.qty AS qtyIn, tr.POLineID
FROM dbo.tblTRLine AS tr LEFT OUTER JOIN
dbo.tblTRLedger AS trL ON tr.TRLedgerID = trL.TRLedgerID
WHERE (trL.locationID = 6)
that code works well and I get the proper totals per POLineID.
I have the table that I should use for subtracting as
/* All Landed Inventory */
SELECT tr.POLineID, SUM(tr.qty) AS qtyOut
FROM dbo.tblTRLine AS tr LEFT OUTER JOIN
dbo.tblTRLedger AS trL
ON tr.TRLedgerID = trL.TRLedgerID
WHERE (trL.locationID = 3) OR (trL.locationID = 4)
GROUP BY tr.POLineID
this always works on its own properly grouping by POLineID
Now I want to take the qtyIN - qtyOut for a total result. What I'm doing (full code) is:
/********************************/
/******* Inventory Afloat *******/
/********************************/
/* */
/* */
/* Inventory Purchased Afloat */
/********************************/
WITH InvAtSea AS
(
/* Inventory Purchased Afloat */
SELECTunitQty AS qtyIn, POLineID
FROM dbo.tblPOLine AS po
WHERE (locationID = 6)
UNION
/* Inventory Purchased At Orgin Shipped */
SELECT tr.qty AS qtyIn, tr.POLineID
FROM dbo.tblTRLine AS tr LEFT OUTER JOIN
dbo.tblTRLedger AS trL ON tr.TRLedgerID = trL.TRLedgerID
WHERE (trL.locationID = 6)
),
/* All Landed Inventory */
InvLanded AS
(
SELECT tr.POLineID, SUM(tr.qty) AS qtyOut
FROM dbo.tblTRLine AS tr LEFT OUTER JOIN
dbo.tblTRLedger AS trL
ON tr.TRLedgerID = trL.TRLedgerID
WHERE (trL.locationID = 3) OR (trL.locationID = 4)
GROUP BY tr.POLineID
)
SELECT ino.qtyIn - ISNULL(out.qtyOut, 0) as qtyAtSea
FROMInvAtSea AS ino
LEFT OUTER JOIN
InvLanded AS out
ON ino.POLineID = out.POLineID
but the results don't work.
It is applying the subtraction multiple times, not on the aggregate results.
Can you tell me what I'm missing?
Many thanks,
Mark
June 29, 2011 at 12:21 pm
I guess you're missing the "aggregated result" you mentioned in your comment.
There's not much more to say unless we have table def and sample data for the tables involved together with your expected result. Please see the first link in my signature on how to provide those data in a ready to use format.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply