Hcs 325 Rhetorical Analysis Phase 2

BMIS 325 Phase II
Part A: Table Creation and Data Loading

Part B: Reports
1.) Human Resources:
Select Regions.RegionName, Countries.CountryName, concat(Employees.LastName, ', ' ,Employees.FirstName) EmployeeName, concat(EmployeeTitles.Title,' - ',Employees.Level) Title, concat(Employees.Salary ,'(USD)') Salary from master.dbo.Employees Employees left join master.dbo.Countries Countries on Employees.CountryID = Countries.CountryID left join master.dbo.EmployeeTitles EmployeeTitles on EmployeeTitles.TitleID = Employees.TitleID left join master.dbo.Regions Regions on Regions.RegionID = Countries.RegionID order by Regions.RegionName asc ,Countries.CountryName asc ,Employees.LastName …show more content…
Project hours select Projects.ProjectName, Projects.HourCapAmount, sum(WorkHours.HoursWorked) hoursworked, Projects.HourCapAmount-sum(WorkHours.HoursWorked) Difference from master.dbo.Projects Projects left join master.dbo.WorkHours WorkHours on WorkHours.ProjectID = Projects.ProjectID

where Projects.HourCapAmount <>0 group by Projects.ProjectName, Projects.HourCapAmount

b.) Project Hour Details select Projects.ProjectName, concat(Employees.FirstName, ' ' ,Employees.LastName), sum(WorkHours.HoursWorked) hoursworked from master.dbo.Projects Projects left join master.dbo.WorkHours WorkHours on WorkHours.ProjectID = Projects.ProjectID left join master.dbo.Employees Employees on Employees.EmpID = …show more content…
from master.dbo.Projects Projects left join master.dbo.WorkHours WorkHours on WorkHours.ProjectID = Projects.ProjectID

where Projects.HourCapAmount <>0 group by projects.ProjectID, Projects.HourCapAmount) projectsover on projectsover.projid = projects.ProjectID and projectsover.hoursworked2 > Projects.HourCapAmount

group by Projects.ProjectName, concat(Employees.FirstName, ' ' ,Employees.LastName)

5.) Payroll select concat(Employees.FirstName,' ',Employees.LastName) Fullname, Countries.CountryName, Countries.WeeklyHours, weeklyhours1.week1 - Countries.WeeklyHours overtime_wk1 from master.dbo.Employees Employees left join master.dbo.Countries Countries on Countries.CountryID = Employees.CountryID left join master.dbo.WorkHours WorkHours on WorkHours.EmpID = Employees.EmpID inner join (select Employees.EmpID empid, sum(WorkHours.HoursWorked) week1 from master.dbo.Employees Employees left join master.dbo.WorkHours WorkHours on WorkHours.EmpID = Employees.EmpID where WorkHours.WH_Day between 1 and 7 and WorkHours.WH_Month = 4 group by Employees.EmpID) weeklyhours1 on weeklyhours1.empid = Employees.EmpID and weeklyhours1.week1 >