SQL statement to compute employee pay for a year at different pay rates

[Chad Richardson]

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.

I know I could create a series of UNIONs and hard code the effective dates,
i.e.

select from time sheets where employee=john and timesheet.task_date between
jan 1 and jun 1, compensation.billable rate * timesheet.billable hours

UNION

select from time sheets where employee=john timesheet.task_date between jun
1 and dec 31 compensation.billable_rate * timesheet.billable_hours

I'd have to do that for every employee in a very large SQL.

Is there an easier way using straight SQL? If not could it be done with a
stored procedure?

Thanks for any insight.

[Paul]

To do this it's necessary to know what your tables look like. In a
normalised db of this type you'd probably have 3 tables involved

EMPLOYEES
PAY RATES
TIMESHEET

with proper primary and foreign key relationships. If you have this kind
of arrangement then it's as easy as falling off a log. If not then it
depends what data you actually do have.

--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

[Erland Sommarskog]

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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx