Question:
[Joe Celko’s Production Report Puzzle]

We get a production report from work centers which has a date, center code and how many widgets were produced from each batch of raw materials sent to them that day. It looks like this:

    CREATE TABLE Production        (center INTEGER NOT NULL,        wkdate DATE NOT NULL,        batchno INTEGER NOT NULL,        widgets INTEGER NOT NULL,        PRIMARY KEY (center, wkdate, batchno));

The boss comes in and says he wants to know the average number of widgets Produced by date and center. We say no problem and do it. The next day he comes back and wants the same data in groups of three. In other words, if on 1994 Feb 24 in center 42, we processed nine batches, the report will show the average number of widgets made from the first three batches, the second three batches and the last three batches.

Answer:
The first query is very straightforward:

    SELECT center, wkdate, COUNT(batchno), AVG(widgets)        FROM Production        GROUP BY center, wkdate;

We have to make some assumptions about the second query and decide how to handle daily work where the number of batches is not evenly divisible by three. I am throwing anyone on the border into the lower third; thus, eight batches would be in ordered thirds of 3, 3 and 2 batches each. We can assume the batches are numbered from 1 to (n) for each day’s work and make life easier.

Using the CASE expression in SQL-92, we can find which third a batchno is in with a VIEW, thus:

    CREATE VIEW Prod3 (center, wkdate, third, widgets)        AS SELECT center, wkdate,            CASE WHEN batchno <= MAX(batchno)/3 THEN 1            WHEN batchno > (2*MAX(batchno))/3 THEN 3            ELSE 2            END, widgets        FROM Production;

If you do not have this in your SQL, then you might try something like this:

    CREATE VIEW Prod3 (center, wkdate, third, batchno, widgets)        AS SELECT center, wkdate, 1, batchno, widgets            FROM Production AS P1            WHERE batchno <= (SELECT MAX(batchno)                FROM Production AS P2                WHERE P1.center = P2.center                    AND P1.wkdate = P2.wkdate)        UNION        SELECT center, wkdate, 2, batchno, widgets            FROM Production AS P1            WHERE batchno > (SELECT MAX(batchno)                FROM Production AS P2                WHERE P1.center = P2.center                    AND P1.wkdate = P2.wkdate)            AND batchno <= (SELECT 2 * MAX(batchno)                FROM Production AS P2                WHERE P1.center = P2.center                    AND P1.wkdate = P2.wkdate)        UNION        SELECT center, wkdate, 3, batchno, widgets            FROM Production AS P1            WHERE batchno > (SELECT 2 * MAX(batchno)                FROM Production AS P2                WHERE P1.center = P2.center                    AND P1.wkdate = P2.wkdate);

Either way, we end up with the final query:

    SELECT center, wkdate, third, COUNT(batchno), AVG(widgets)        FROM Prod3        GROUP BY center, wkdate, third;

Puzzle provided courtesy of:
Joe Celko
[email protected]