Calculating Hourly Billings

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]

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS