how to query for time based inventory status

From: <john_woo_at_canada.com>
Date: Thu, 27 Sep 2007 08:13:56 -0700
Message-ID: <1190906036.612382.267300@d55g2000hsg.googlegroups.com>

Hi,

I'm wondering whether it's possible to query time based inventory status/quantity.

basically there are buying in (quantity increased), selling out
(quantity decreased), renting out (quantity temporary decreased) and
renting in (quantity temporary increased) events.

ex. the records in the inventory table as:

inventory_id || start_timestamp || end_timestamp || quantity || events_type

   1               ||       2000.1.31 10:15   ||
----                   ||    1000     ||   buying in
   1               ||        2000.2.10 9am    ||      2000.2.15 4on
||   -  30     ||    renting out
   1               ||        2000.3.19 12pm  ||
---                    ||    -  10      ||    selling out
   1               ||        2000.2.1  4pm     ||     2000.3.1 9am
||     100      ||    renting in

.....

apparently, no end_timestamp value for buying/selling; negative quantity means inventory decreased.

The rows can grow up to 1 million.

question:
how to know the available quantity for inventory_id 1 given a period
(ex 2000.2.15 10am - 2000.2.20 4pm) ?

Thanks
John Received on Thu Sep 27 2007 - 10:13:56 CDT