Calculating Hourly Billings

Calculating Hourly Billings

[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.

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,, 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,;
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:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes