how to query for time based inventory status

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

john_woo@canada.com - 27 Sep 2007 16:13 GMT

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

Reply to this Message

-CELKO- - 29 Sep 2007 01:40 GMT

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?

Reply to this Message

john_woo@canada.com - 30 Sep 2007 15:24 GMT

> First, download a copy of the Rick Snodgrass book on temporal queries
> in SQL from the University of Arizona website. It has code samples
[quoted text clipped - 23 lines]
> 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

Reply to this Message

Tonkuma - 03 Oct 2007 20:33 GMT

This must be not so elegant solution(especially not fully utilising
capabilities of set oriented operations).
I would be very glad if someone showed us more elegant ways.
(I added some more rows. And tested on DB2 9 for LUW.)

Examples of queries:
1) Lowest available quantity for each periods.

Brief logic is .....
1-1) Find the all points of time in which it is possible to change
inventory.
1-2) Find all periods starting from the time found in step 1) and next
of that time.
1-3) Calculate invetoriers for each periods found in step 2).

------------------------- Commands Entered -------------------------
WITH all_points (change_timestamp) AS (
SELECT start_timestamp FROM Inventory
WHERE inventory_id = 1
UNION
SELECT COALESCE(end_timestamp, '2999-12-31-23.59.59') FROM Inventory
WHERE inventory_id = 1
)
,peoriods (start_timestamp, end_timestamp) AS (
SELECT change_timestamp
, MAX(change_timestamp)
OVER(ORDER BY change_timestamp ROWS BETWEEN 1 FOLLOWING AND 1
FOLLOWING)
FROM all_points
)
SELECT P.start_timestamp, P.end_timestamp
, SUM(quantity) availables
FROM peoriods P
INNER JOIN
Inventory I
ON I.inventory_id = 1
AND I.start_timestamp <= P.start_timestamp
AND COALESCE(I.end_timestamp, '2999-12-31-23.59.59') >=
P.end_timestamp
GROUP BY
P.start_timestamp, P.end_timestamp
ORDER BY
P.start_timestamp, P.end_timestamp
;
--------------------------------------------------------------------

START_TIMESTAMP END_TIMESTAMP AVAILABLES
-------------------------- -------------------------- -----------
2000-01-31-10.15.00.000000 2000-02-01-16.00.00.000000 1000
2000-02-01-16.00.00.000000 2000-02-10-09.00.00.000000 1100
2000-02-10-09.00.00.000000 2000-02-13-09.00.00.000000 1070
2000-02-13-09.00.00.000000 2000-02-15-16.00.00.000000 1050
2000-02-15-16.00.00.000000 2000-02-18-16.00.00.000000 1080
2000-02-18-16.00.00.000000 2000-02-20-09.00.00.000000 1100
2000-02-20-09.00.00.000000 2000-02-24-16.00.00.000000 1055
2000-02-24-16.00.00.000000 2000-02-25-16.00.00.000000 1105
2000-02-25-16.00.00.000000 2000-02-27-09.00.00.000000 1150
2000-02-27-09.00.00.000000 2000-03-01-09.00.00.000000 1130
2000-03-01-09.00.00.000000 2000-03-10-16.00.00.000000 980
2000-03-10-16.00.00.000000 2000-03-19-12.00.00.000000 1000
2000-03-19-12.00.00.000000 2999-12-31-23.59.59.000000 990

13 record(s) selected.

2) Find avaiable quantity for specified period.
(Sample period is from '2000-2-15-16.00.00' to '2000-2-28-09.00.00')
------------------------- Commands Entered -------------------------
WITH all_points (change_timestamp) AS (
SELECT start_timestamp FROM Inventory
WHERE inventory_id = 1
UNION
SELECT COALESCE(end_timestamp, '2999-12-31-23.59.59') FROM Inventory
WHERE inventory_id = 1
)
,peoriods (start_timestamp, end_timestamp) AS (
SELECT change_timestamp
, MAX(change_timestamp)
OVER(ORDER BY change_timestamp ROWS BETWEEN 1 FOLLOWING AND 1
FOLLOWING)
FROM all_points
)
,available_in_peoriods(start_timestamp, end_timestamp, availables) AS
(
SELECT P.start_timestamp, P.end_timestamp
, SUM(quantity) availables
FROM peoriods P
INNER JOIN
Inventory I
ON I.inventory_id = 1
AND I.start_timestamp <= P.start_timestamp
AND COALESCE(I.end_timestamp, '2999-12-31-23.59.59') >=
P.end_timestamp
GROUP BY
P.start_timestamp, P.end_timestamp
)
SELECT S.start_timestamp, S.end_timestamp
, MIN(availables) available
FROM available_in_peoriods P
, (VALUES ('2000-2-15-16.00.00', '2000-2-28-09.00.00') )
S(start_timestamp, end_timestamp)
WHERE P.start_timestamp < S.end_timestamp
AND P.end_timestamp > S.start_timestamp
GROUP BY
S.start_timestamp, S.end_timestamp
;
--------------------------------------------------------------------

START_TIMESTAMP END_TIMESTAMP AVAILABLE
------------------ ------------------ -----------
2000-2-15-16.00.00 2000-2-28-09.00.00 1055

1 record(s) selected.

Reply to this Message