Re: how to query for time based inventory status

From: <john_woo_at_canada.com>
Date: Sun, 30 Sep 2007 07:24:26 -0700
Message-ID: <1191162266.844032.106460_at_n39g2000hsh.googlegroups.com>

On Sep 28, 8:40 pm, -CELKO- <jcelko..._at_earthlink.net> wrote:
> First, download a copy of the Rick Snodgrass book on temporal queries
> in SQL from the University of Arizona website. It has code samples
> and everything you will need.
>
> What you have here is mixed models in one table. Sometimes
> start_timestamp is part of a duration and sometimes it is a point in
> time for an event.
>
> The (start_timestamp, end_timestamp) should consistently model the
> state of the inventory for a temporal interval. A NULL in the
> end_timestamp means that this is the current state of affairs and you
> use COALESCE (end_timestamp, CURRENT_TIMESTAMP) in VIEWs and queries.
> When it changes, update the NULL to an actual timestamp and start a
> new row that reflects the change.
>
> I like having a running total since it is less work for queries, but
> you could have the delta for that period instead. I also prefer to
> normalize the table by having the actions against the inventory in its
> own table.
>
> >> the available quantity for inventory_id [in a] given a period <<
>
> Bad question; if the search period fits inside the (start_timestamp,
> end_timestamp) range of a single row, then life is easy. But what do
> you mean if the search period has several (start_timestamp,
> end_timestamp) ranges in it? Average inventory level? Final
> inventory level? Something else?

Thanks lots for all the info. I'll try it.

Well, it's not a "Bad question", the difficulty is beyond "the search period has several ranges in it".
ex. supposed on the first date,

a. 10am buying in 10;
b. 2pm selling out 2;
c.  3 pm- next date renting out 5;
d. 8pm (return from c) coming in 4;
...

then on the first date, the available quantity is: 10am-2pm 10;

2pm-3pm 3 (10-2-5=3);
3pm-8pm 3 (nothing happened);
8pm-next date opened time 7

thus, for a given period, ex
10am-10am, the quantity on store is 10;
10am-4pm, the quantity is 3;
...

BTW, the "replace null timestamp" may not be good idea in business point of view, as selling and buying (no cancel action) will affect quantity from the time happened until forever.

another difficulty is, how to optimize the query.

Regards,

John Received on Sun Sep 30 2007 - 16:24:26 CEST