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