Re: how to query for time based inventory status

From: Tonkuma <tonkuma_at_fiberbit.net>
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 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