How to Aggregate Data with Variable Dates

[jprice]

I have an inventory table in my database with approximately 180,000 records. I am trying to show the sellthrough rate for each product from a specific date until now. To do that, I need to see the current stock for each product, and its stock at the start of the date range.   The problem is that start date for each product within that range is variable since certain products arrive in stock at different times.  I can get the earliest date for each product within the range by using the MIN statement, but the challenge is to show what the SUM for each product is on that MIN date.

 

In my example below, I attempted to capture the MIN date in a subquery, but doing so causes the database to hang. 

 

ExecuteSQL(

" SELECT id_product, entry_date, SUM ( stock )
  FROM inventory a
  WHERE entry_date IN

    ( SELECT MIN ( entry_date )
      FROM inventory b
      WHERE a.id_product = b.id_product

      AND b.entry_date >= ? )

   GROUP BY id_product, entry_date "

 "" ; "" ; $start_range

)  // end of sql

 

I also attempted a LEFT OUTER JOIN

 

ON a.id_product=b.id_product

AND a.entry_date >= b.entry_date

AND b.id_product IS NULL

 

This froze the database too.

 

Anyone have any ideas?

[Wim Decorte]

Purely from an architectural point of view, you should not need to have to calculate this at all, each movement in or out of the warehouse is a transaction that updates the inventory level of the product.  So your transactions table should be able to tell you exactly what the numbers are for any date range.  Or am I missing something?

 

So while you'd still need a SQL statement to figure out what the start and end quantity is for each product, you would only have to query one table and not have to do joins or sums.

[jprice]

I am trying to turn a 2 step process into one, using ExecuteSQL.  Step one: get the MIN ( date ) for each product in my database.  Step two: get the SUM ( total stock ) for each product using the date for each product that is aquired in step one.

 

At the moment, the only technique that works for me is to perform step one, then use a script that grabs the MIN ( date ) to get the SUM ( total stock ) one product at a time.

 

If there were a way to fetch first row by group, I could sort by date and only grab the first result for each product, but sadly fetch first does not work that way.  I am stumped.

[Lee Smith]

I merged your two topics because the new one is a duplicate post. If you want to add information, just reply to the original topic