select sum (onHand-Ordered) from (select sum (qtyOnHand) from tb1 where item = RD35 group by item) as onHand, (select sum (qtyOrdered) from tb2 where item = RD35 group by item) as Ordered
I kind of gathered it would work based on thishttp://weblogs.asp.net/jgalloway/archive/2004/05/19/135358.aspx
I have also tried this;
select tb1.item from (select sum (qtyOnHand) from tb1 where item = RD35 group by item) as onHand, (select sum (qtyOrdered) from tb2 where item = RD35 group by item) as Ordered, sum (onHand-Ordered) as available from tb1 where tb1.item = RD35
Any ides, there are multiple rows of each item in each table tb1 is inventory with several different locations and tb2 is an orders table.
I'm not sure exactly what values you are looking for but it looks like you want the new quantity on hand after the order for a particular item. Try this:
SELECT
tb1.Item,
IsNull(tb1.qtyOnHand - SUM(qtyOrdered), 0) AS qtyNewOnHand
FROM tb1
LEFT JOIN tb2 ON tb1.item = tb2.item
WHERE tb1.item = RD35
GROUP BY tb1.item, tb1.qtyOnHand
I assumed that tb1 has 1 record per item but that tb2 may have multiple records (orders).
HTH.
Thanks for the reply. There a multiple records in table 1 for a single item, it is an inventory by location table where bin numbers are the primary key. There are multiple records in table 2 as well. Which is why unions and joins are biting me with some multplication. In this particular case RD35 has 3 records in table 1 with a sum of 237 and 6 records in table 2 with a sum of 97.
select table1.item from (select sum (qtyOnHand) from table1 where item = RD35 group by item) as onHand, (select sum (qtyOrdered) from table2 where item = RD35 group by item) as Ordered, sum (onHand-Ordered) as available from table1 where table1.item = RD35
This ends up giving me; onHand 237, Ordered 97 and vailable 402. available should be 140.
Try this:
SELECT
tbOnHand.item,
OnHand,
IsNull(Ordered, 0),
OnHand - IsNull(Ordered, 0) AS Available
FROM
(
SELECT
item,
SUM(qtyOnHand) as OnHand
FROM tb1
WHERE item = RD35
GROUP BY item
) AS tbOnHand
LEFT JOIN
(
SELECT
item,
SUM(qtyOrdered) as Ordered
FROM tb2
WHERE item = RD35
GROUP BY item
) AS tbOrdered ON tbOnHand.item = tbOrdered.item
No comments:
Post a Comment