Re: how to query for time based inventory status

From: Tonkuma <tonkuma_at_fiberbit.net>
Date: Wed, 03 Oct 2007 12:33:57 -0700

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. Received on Wed Oct 03 2007 - 21:33:57 CEST

Original text of this message