Question:
[Joe Celko’s Consultants Puzzle]

Brian K. Buckley posted a version of the following problem on the PowerSoft CompuServe forum in the WATCOM SQL section in 1994 November requesting assistance. He has three tables, defined as:

    CREATE TABLE Consultants        (empid INTEGER NOT NULL,        name CHAR(10) NOT NULL);    INSERT INTO Consultants VALUES (1, ‘Larry’);    INSERT INTO Consultants VALUES (2, ‘Moe’);    INSERT INTO Consultants VALUES (3, ‘Curly’);    CREATE TABLE Billings        (empid INTEGER NOT NULL,        effectdate DATE NOT NULL,        billrate DECIMAL (5,2));    INSERT INTO Billings VALUES (1, ‘1990-01-01’, 25.00);    INSERT INTO Billings VALUES (2, ‘1989-01-01’, 15.00);    INSERT INTO Billings VALUES (3, ‘1989-01-01’, 20.00);    INSERT INTO Billings VALUES (1, ‘1991-01-01’, 30.00);    CREATE TABLE HoursWorked        (jobid INTEGER NOT NULL,        empid INTEGER NOT NULL,        workdate DATE NOT NULL,        billhours DECIMAL(5, 2));    INSERT INTO HoursWorked VALUES (4, 1, ‘1990-07-01’, 3);    INSERT INTO HoursWorked VALUES (4, 1, ‘1990-08-01’, 5);    INSERT INTO HoursWorked VALUES (4, 2, ‘1990-07-01’, 2);    INSERT INTO HoursWorked VALUES (4, 1, ‘1991-07-01’, 4);

He wanted a single query that would show a list of names and total charges for a given jobid. Total charges are calculated for each employee as the hours worked by then applicable hourly billing rate. For example, the sample data shown would give the answer:

    Results    name   totalcharges    ===================    Larry   320.00    Moe      30.00

since Larry would have ((3+5) hours * $25 rate + 4 hours * $30 rate) = $320.00 and Moe (2 hours * $15 rate) = $30.00.

Answer:
I think the best way to do this is to build a VIEW, then summarize from it. The VIEW will be handy for other reports. This gives you the VIEW:

    CREATE VIEW         HourRateRpt (empid, name, workdate, billhours, billrate)     AS SELECT H1.empid, name, workdate, billhours,         (SELECT billrate            FROM Billings AS B0            WHERE effectdate = (SELECT MAX(effectdate)                FROM Billings AS B1                WHERE B1.effectdate <= H1.workdate                    AND B0.empid = B1.empid                    AND B0.empid = H1.empid))        FROM HoursWorked AS H1, Consultants AS E1        WHERE E1.empid = H1.empid;

Then your report is simply:

    SELECT empid, name, SUM(billhours * billrate) AS totbill        FROM HourRateRpt        GROUP BY empid, name;

But since Mr. Buckley wanted it all in one query, this would be his requested solution:

    SELECT C1.empid, C1.name, SUM(billhours) *        (SELECT billrate            FROM Billings AS B0            WHERE effectdate = (SELECT MAX(effectdate)                FROM Billings AS B1                WHERE B1.effectdate <= H1.workdate                    AND B0.empid = B1.empid                    AND B0.empid = H1.empid))        FROM HoursWorked AS H1, Consultants AS C1        WHERE H1.empid = C1.empid        GROUP BY C1.empid, C1.name;

This is NOT an obvious answer for a beginning SQL programmer, so let’s talk about it. Start with the innermost query, which picks the effective date of each employee which immediately occurred before the date of this billing. The next level of nested query uses this date to find the billing rate that was in effect for the employee at that time; that is why the outer correlation name B0 is used. Finally, the billing rate is returned to the expression in the SUM() function and multiplied by the number of hours worked. Finally, the outermost query groups each employee’s billings and produces a total.

Puzzle provided courtesy of:
Joe Celko
[email protected]