CODEWITHSUNDEEP
sqlsql-server
T2true
T2true

Reputation: 61

How to multiply the results of two different queries

I have three tables: Employee, BillingRates, and WorkHours.

Employees
------------------------------
EmpID | name | titleID | level  

BillingRates
----------------------
titleID | level | rate

WorkHours
-------------------
EmpID | HoursWorked

This query gives me the names and rate for each employee: 

select 
    firstname, lastname, rate
from employees 
left join billingrates
    on employees.TitleID = BillingRates.TitleID
    and employees.Level = BillingRates.Level

This gives me the EmpID and total hours worked by that employee:

select 
    employees.EmpID, sum(workhours.hoursworked) as TotalHours
from employees 
inner join workhours
    on employees.empid = WorkHours.EmpID
where 
    WH_Month = 4
group by 
    LastName, firstname, employees.EmpID

I need to multiply the rate output by the total hours worked by that employee.
Would someone be able to assist with writing a query that generates that output?

Upvotes: 2

Views: 1281

Answers (1)

BICube
BICube

Reputation: 4681

You can do something like this in SQL server

select employees.EmpID, sum(workhours.hoursworked) as 'TotalHours',
       firstname, lastname, 
       ISNULL(sum(workhours.hoursworked),0) * ISNULL(min(rate),0) AS 'TotalRate'
from employees inner join workhours
on employees.empid = WorkHours.EmpID
LEFT JOIN BillingRates ON employees.titleid=billingrates.titleid
AND employees.level = billingrates.level
where WH_Month = 4
group by LastName, firstname, employees.EmpID

Upvotes: 3

Related Questions