Posted by Erland Sommarskog on 11/17/05 01:03

Chad Richardson (chad@NIXSPAM_chadrichardson.com) writes:
> I would like a single SQL to return all employee's total billable
> compensation for a year. Their billable rates change throughout the year
> so under the employee table (one), there is a compensation table (to
> many) which has the employee id, effective date, billable hourly rate.
> So in a given year calendar year they could have many different (though
> usually 2 at most) rates. These rates then have to correspond to and e
> multiplied by their corresponding billable hours from the time sheet
> table.

Assuming that the timesheet table looks something like:

CREATE TABLE timesheets
(empid int NOT NULL,
day datetime NOT NULL,
billablehours tinynt NOT NULL)

You could do:

SELECT e.empname, SUM(c.rate * t.billablehours)
FROM employees e
JOIN compensations c1 ON e.empid = c1.empid
JOIN timesheets ON t.day >= c1.effective_date AND
t.day < (SELECT MIN(c2.effective_date)
FROM compensations c2
WHERE c2.empid = c1.empid
AND c2.effective_date > c1.effective_date)

This is a bit of a wild guess, and also untested. If this does not seme to
answer your problem, please include the following:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The expected output given the sample data.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]