SQL is the first programming language to have native temporal data types and operators in it. I have always thought that if only COBOL has a DATE datatype, there would have been no Y2K crisis.

Time is a different kind of animal from other datatypes and periods of time are conceptual hard in SQL. As an example, try this problem. You have an inventory of clothing represented by this table:

CREATE TABLE Inventory
(stock_date DATE NOT NULL,
 style CHAR(7) NOT NULL,
 size CHAR(3) NOT NULL,
 inv_qty INTEGER NOT NULL CHECK(inv_qty > 0),
 PRIMARY KEY (stock_date, style, size));

-- populate Inventory table
 INSERT INTO Inventory
  VALUES ('2000-07-01', '29M', 'XL', 50);
 INSERT INTO Inventory
  VALUES ('2000-07-02', '29M', 'XL', 40);
 INSERT INTO Inventory
  VALUES ('2000-07-03', '29M', 'XL', 65);
 INSERT INTO Inventory
  VALUES ('2000-07-04', '29M', 'XL', 60);
 INSERT INTO Inventory
  VALUES ('2000-07-01', '29M', '2X', 50);
 INSERT INTO Inventory
  VALUES ('2000-07-02', '29M', '2X', 40);
 INSERT INTO Inventory
  VALUES ('2000-07-03', '29M', '2X', 65);
 INSERT INTO Inventory
  VALUES ('2000-07-04', '29M', '2X', 60);
 INSERT INTO Inventory
  VALUES ('2000-07-01', '562M', 'XXL', 50);
 INSERT INTO Inventory
  VALUES ('2000-07-02', '562M', 'XXL', 40);
 INSERT INTO Inventory
  VALUES ('2000-07-03', '562M', 'XXL', 65);
 INSERT INTO Inventory
  VALUES ('2000-07-04', '562M', 'XXL', 60);

The stock_date is the inventory level on that date. We also have a table of the forecasted demand for the following days:

CREATE TABLE Demand
(stock_date DATE NOT NULL,
 style CHAR(7) NOT NULL,
 size CHAR(3) NOT NULL,
 dmd_qty INTEGER NOT NULL DEFAULT 0,
 PRIMARY KEY (stock_date, style, size));

-- populate Demand table
 INSERT INTO Demand VALUES ('2000-07-02, '29M', 'XL', 10);
 INSERT INTO Demand VALUES ('2000-07-03, '29M', 'XL', 20);
 INSERT INTO Demand VALUES ('2000-07-04, '29M', 'XL', 25);
 INSERT INTO Demand VALUES ('2000-07-05, '29M', 'XL', 40);
 INSERT INTO Demand VALUES ('2000-07-02, '29M', '2X', 50);
 INSERT INTO Demand VALUES ('2000-07-03, '29M', '2X', 10);
 INSERT INTO Demand VALUES ('2000-07-04, '29M', '2X', 25);
 INSERT INTO Demand VALUES ('2000-07-05, '29M', '2X', 20);
 INSERT INTO Demand VALUES ('2000-07-02, '562M', 'XXL', 10);
 INSERT INTO Demand VALUES ('2000-07-03, '562M', 'XXL', 20);
 INSERT INTO Demand VALUES ('2000-07-04, '562M', 'XXL', 25);
 INSERT INTO Demand VALUES ('2000-07-05, '562M', 'XXL', 40);

The question is, how many future days of stock do we have to meet the projected demand for each stock date? Notice that we have inventory levels for each of fours days and demand for four days, starting one day after the inventory. For example, on 2000-07-01, we have 50 units of 29M in extra large (XL) in stock. On 2000-07-02, we have used 10 units, leaving 40 in inventory; on 2000-07-03 we have used 20 more units, leaving 20 in inventory; on 2000-07-04 we have used 25 units, leaving inventory five units short. So we have two days of demand for this item on 2000-07-01.

You could use a cursor, but we really want to avoid a cursor. A first attempt at solving this problem is to build a query that will deduct the running total of the demand from the current inventory level starting tomorrow.

SELECT I1.stock_date, I1.style, I1., I1.size, I1.inv_qty,
       (I1.inv_qty -
       (SELECT SUM(D1.dmd_qty)
          FROM Demand AS D1
         WHERE D1. = I1.
           AND D1.size = I1.size
           AND D1.style = I1.style
           AND D1.demand_date
               = I1.stock_date
                 + INTERVAL 1 DAY))AS forecast_1,
       (I1.inv_qty -
       (SELECT SUM(D2.dmd_qty
          FROM Demand AS D2
         WHERE D2.size = I1.size
           AND D2.style = I1.style
           AND D2.stock_date
               BETWEEN I1.stock_date + INTERVAL 1 DAY
                   AND I1.stock_date + INTERVAL 2 DAYS))
       AS forecast_2,
       (I1.inv_qty -
       (SELECT SUM(D3.dmd_qty)
          FROM Demand AS D3
         WHERE D3.size = I1.size
           AND D3.style = I1.style
           AND D3.stock_date      
                BETWEEN I1.stock_date + INTERVAL 1 DAY
                    AND I1.stock_date + INTERVAL 3 DAYS))
       AS forecast_3,
       (I1.inv_qty -
       (SELECT SUM(D4.dmd_qty)
          FROM Demand AS D4
         WHERE D4.size = I1.size
           AND D4.style = I1.style 
           AND D4.stock_date
               BETWEEN I1.stock_date + INTERVAL 1 DAY
                   AND I1.stock_date + INTERVAL 4 DAYS))
        AS forecast_4
   FROM Inventory AS I1;

This nightmare query will give you the projections of inventory levels for one, two, three, and four days. It will also run like glue, is hard to read, and it also stops at four days. You know that you will run out of an item when you see a negative number in one of the forecast column, but you do not know how many days of demand you can meet.

Let’s try again. What we really want to know is a count of the days left in the inventory for each item. Instead of trying to design the query from the viewpoint of the inventory, let’s do it from the viewpoint of the demand.

SELECT D1.demand_date, D1.style, D1.size,
       (SUM(I1.inv_qty)/D1.dmd_qty) AS forecasted_weeks
 FROM Inventory AS I1,
      Demand AS D1WHERE D1.size = I1.size
  AND D1.style = I1.style
  AND D1.demand_date > I1.stock_date
GROUP BY D1.demand_date, D1.style, D1.size;

Please notice that forecasted_weeks will be the result of an integer division; you could cast the datatypes to get a decimal fraction, but whole numbers are more useful in this case — you cannot get half a dress.

But don’t stop here. This query will tell you how many full weeks of inventory are left for a given year_week.

SELECT I1.style, I1.color, I1.size, I1.inv_qty,
      I1.corp_year_week, MAX(D4.end_week) AS final_week,
      (MAX(D4.end_week) - I1.corp_year_week) AS full_weeks
FROM DSO_inventory AS I1
      INNER JOIN
     (SELECT D1.color, D1.size, D1.style,
             SUM(D3.dmd_qty) AS tot_dmd_qty,
             D1.corp_year_week AS start_week,
             D2.corp_year_week AS end_week
       FROM DSO_demand AS D1,
            DSO_demand AS D2,
            DSO_demand AS D3
      WHERE D3.corp_year_week
            BETWEEN D1.corp_year_week
                AND D2.corp_year_week
        AND D1.color = D2.color
        AND D1.color = D3.color
        AND D1.size = D2.size
        AND D1.size = D3.size
        AND D1.style = D2.style
        AND D1.style = D3.style
      GROUP BY D1.corp_year_week, D2.corp_year_week,
            D1.color, D1.size, D1.style) AS D4
      ON D4.color = I1.color
         AND D4.size = I1.size
         AND D4.style = I1.style
         AND :my_year_week + 1 = D4.start_week
         AND I1.inv_qty >= D4.tot_dmd_qty
         AND I1.corp_year_week = :my_year_week 
  GROUP BY I1.style, I1.color, I1.size, I1.inv_qty,
           I1.corp_year_week;

The idea is to build a derived table of start and stop dates withthe total demand for each product between those dates. I then join in to Inventory, use :my_year_week as the report date. If inventory is more than the cumulative demand, then you have a full week’s worth up to that week.

--

Joe Celko was a member of the ANSI X3H2 Database Standards Committee and helped write the SQL-92 standards. He is the author of over 450 magazine columns and four books, the best known of which is SQL for Smarties (Morgan-Kaufmann Publishers, 1999). He is the Vice President of RDBMS at Northface University in Salt Lake City.