vendredi 8 mai 2015

Retrieving values from multiple tables

I am working on a school project and I'm stuck.

The requirement is to write a single query that gathers data from nine different tables and returns a result.

It needs the client names, contract names, project names, employees that logged hours for April, total hours logged by employee, employee rate, charges per employee for the projects, and the contact names for each contract.
(I know, that's a lot of data to pull.)

The tables are:

 Clients   
-------------------------------
ClientID | CommonName  

 ContractTypes   
--------------------------------------  
ContractTypeID | ContractType   

 Contracts   
---------------------------------------------------------  
ContractID | ClientID (FK) | ContractType (FK)   

 Projects   
---------------------------------------------------  
ProjectID | ProjectName | ContractID (FK)   

 Contacts   
-----------------------------------------  
ContactID | FirstName | LastName  

 ContractContacts   
--------------------------------------------------  
ContractID (PK/FK) | ContactID (PK/FK)   

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

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

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

I ran the following, but it resulted is over 23,000 rows - not what is needed:

select 
   clients.CommonName, projects.ProjectName, employees.FirstName,  
   employees.lastname, sum(workhours.hoursworked) as TotalHours,  
   BillingRates.Rate,contacts.FirstName, contacts.LastName,     
   contacts.AddrLine1, contacts.AddrLine2, contacts.City, 
   contacts.State_Province, contacts.Zip
from 
   employees, BillingRates, Clients, Projects, WorkHours, Contracts, Contacts
group by 
   clients.CommonName, projects.ProjectName, employees.FirstName,  
   employees.lastname, BillingRates.Rate, contacts.FirstName, 
   contacts.LastName, contacts.AddrLine1, contacts.AddrLine2, 
   contacts.City, contacts.State_Province, contacts.Zip

I honestly have no idea how to pull in all that data into one query to generate one report.

I received help on generating a query to return the employees that logged hours for April, total hours logged by the employee, and the charges per employee for the projects in the following:

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

I assume this query would be nested into the outer query, but I don't know how to construct it.

Would someone be able to assist with writing this query?

Aucun commentaire:

Enregistrer un commentaire