By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,282 Members | 1,562 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,282 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.
Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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/
Nov 23 '05 #2

P: n/a
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
Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.