get list of products in stock at any given date

Question

I have two tables: one holding products and one holding related inventory moves.

Product table:

Seq | Name | Barcode  

Move table:

Seq | ProductFK | Direction | Date  

Each product can have multiple IN and/or OUT move records.
How can I get a list of all product that are in stock at any given date?

I tried the following but that does not work if there are more than one IN and OUT moves:

SELECT DISTINCT Product.* , Move.* FROM Product LEFT JOIN Move ON Product.Seq=Move.StockFK where not exists ( select * from Move where Product.Seq=Move.StockFK and Direction = "OUT") 

Can anyone help me on this please. I am using MySQL 5.1.

Answer 1

You could create an OLAP cube with inventory, transactions, and time as the dimensions. I'm an MSSQL guy, so I'd do all that in Reporting Server, but I'm certain there are MySQL cube analyzers out there as well.

Quick Google search suggests: www.ramsetcube.com

Answer 2

There's a fundamental problem with the direction column being text/varchar, containing only "in" and/or "out" values. There's no way to know the numbers involved, which means no way to know if stock exists on a given day. If the data type were a number, you could sum (assuming outgoing stock is negative) the columns for a given date.

Assuming the direction column used a numeric data type, use:

SELECT p.seq,
       p.name,
       p.barcode,
       x.stock_onhand
  FROM PRODUCTS p
  JOIN (SELECT m.productfk,
               SUM(m.direction) 'stock_onhand'
          FROM MOVE m 
         WHERE DAY(m.date) = ?
           AND MONTH(m.date) = ?
           AND YEAR(m.date) = ?
      GROUP BY m.productfk, 
               DAY(m.date),
               MONTH(m.date),
               YEAR(m.date)) x ON x.productfk = p.seq

Because the DATETIME & TIMESTAMP data types include the time portion, you have to use functions to isolate the date. Because of the use of a function on the column, an index on the date column couldn't be used - assuming one existed.

Answer 3

I may have misunderstood your question.

Everytime you have "IN" just add the stock count by one. Everytime there is "OUT" just subtract it by one. The difference is the stock?

SELECT ProductFK, SUM(IF(m.direction='IN'))-SUM(IF(m.direction='OUT')) AS stock
FROM Move m WHERE m.Date < '20090101' AND stock > 0

That should do it, now just join it with your Product table if you like.

If you have a quantity field that you omitted just modify the SUM IFs.

Answer 4

Thank you OMG Ponies and eckesicle: I found that a combination of your answers provided me with a working solution:

SELECT p.Seq,
       p.Barcode,
       x.stock_onhand
   FROM Product p
   JOIN (SELECT ProductFK, SUM(IF(m.Direction='IN',1,0))-SUM(IF(m.Direction='OUT',1,0))  'stock_onhand'
      FROM Move m WHERE m.Date < "2010/01/19" GROUP BY ProductFK )
   x ON x.ProductFK = p.Seq where stock_onhand>0