SEO

#1 (**permalink**) 02-29-2008, 01:56 PM

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.

#2 (**permalink**) 02-29-2008, 01:56 PM

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/

#3 (**permalink**) 02-29-2008, 01:56 PM

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

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