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.