Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Mar 24, 1997

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
71062.1056@compuserve.com

DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date