Re: how to query for time based inventory status
Date: Wed, 03 Oct 2007 12:33:57 -0700
Message-ID: <1191440037.958656.219430_at_g4g2000hsf.googlegroups.com>
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 1FOLLOWING)
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. Received on Wed Oct 03 2007 - 21:33:57 CEST