Jump to content

How to Aggregate Data with Variable Dates


 Share

This topic is 2941 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
Share on other sites

This topic is 2941 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.