Question regarding weighted sum in SQL

Hi, I'd be grateful for any help with this question and apologies if the answer is obvious to everyone but me!

I have two tables, hours and rates, that look something like this:

desc hours

Employee Varchar
Consulting Number
Support Number

desc rates

Employee Varchar
Consulting Number
Support Number

which represent the hours worked by each employee by category and the hourly rate billed for each category of work if performed by a given employee. I'd like to write a simple query that calculates the total fee for each employee: that is, (in this case)

Select (hours.consulting*rates.consulting + hours.support*rates.support) from hours, rates where hours.Employee = rates.Employee

There's a catch however: there may be many more work categories created dynamically, and in advance of the total query itself I don't know what these will be nor even how many there are. Is there a way of writing a (Oracle) SQL query that will perform this total?

If need be I can re-create these tables so that employees become columns and the work categories become a column called Type, so:

desc newhours

Type varchar(10)
Employee1 Number
Employee2 Number

and

desc newrates

Type varchar(10)
Employee1 Number
Employee2 Number

Thanks for any help - it's got me stumped... Martin

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Aug 03 1999 - 20:12:49 CDT