Inventory Conundrum [Solved]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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)
  ...
)
josa
Forum Commoner
Posts: 75
Joined: Mon Jun 24, 2002 4:58 am
Location: Sweden

Post 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
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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.
josa
Forum Commoner
Posts: 75
Joined: Mon Jun 24, 2002 4:58 am
Location: Sweden

Post by josa »

Califdon said in SQL what I tried to say in words. I guess you can't avoid a dynamic query...

/josa
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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.
Post Reply