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)
...
)
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.
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.