Re: how to query for time based inventory status

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? Received on Fri Sep 28 2007 - 19:40:12 CDT