Page 2 of 2
Posted: Mon Sep 03, 2007 3:32 pm
by califdon
I'll bet there's a better way, but maybe this ugly query would work??
Code: Select all
SELECT SUM(total) FROM (
(SELECT total FROM tblInventory WHERE item=x AND whse=1 ORDER BY invDate DESC LIMIT 1)
UNION
(SELECT total FROM tblInventory WHERE item=x AND whse=2 ORDER BY invDate DESC LIMIT 1)
UNION
(SELECT total FROM tblInventory WHERE item=x AND whse=3 ORDER BY invDate DESC LIMIT 1)
UNION
(SELECT total FROM tblInventory WHERE item=x AND whse=4 ORDER BY invDate DESC LIMIT 1)
...
)
Posted: Mon Sep 03, 2007 4:09 pm
by josa
You mentioned earlier that you know which warehouses have a certain item. If that's so then this would be one way to go about it:
1. Find out which warehouses has the item
2. Query the transaction table for each warehouse (one query per warehouse)
You can probably do this in a stored procedure (if your mysql version has it) if you want. The problem is doing all this in one query - but if you can split it up like I described above, then it can be done.
/josa
Posted: Mon Sep 03, 2007 8:02 pm
by Begby
Thank you both for your help and suggestions. I'll work on this tomorrow and let you know what I end up doing.
Posted: Tue Sep 04, 2007 1:29 am
by josa
Califdon said in SQL what I tried to say in words. I guess you can't avoid a dynamic query...
/josa
Posted: Tue Sep 04, 2007 2:02 pm
by Begby
Here is what I came up with, this will get the total inventory across all warehouses for a given date
Code: Select all
SELECT SUM(ih1.inventoryCount) AS grandTotal
FROM tblItemHistory ih1
INNER JOIN
(
SELECT max(ih2.itemHistoryDate) AS latestDate,
ih2.warehouseID
FROM tblItemHistory ih2
WHERE ih2.itemHistoryDate <= '2007-09-04 10:18:46'
AND ih2.itemID = 10
GROUP BY warehouseID
) AS j1 ON j1.latestDate = ih1.itemHistoryDate AND ih1.warehouseID = j1.warehouseID
Since I am using MySQL I cannot use the above as a scalar subquery since you can't correlate subqueries used in a from clause. So that means I need to use 2 queries to get all the rows which I think will be just fine.