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.