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
asc ,EmployeeTitles.Title asc ,Employees.Salary asc

2.) Invoicing
Select Clients.CommonName, Contracts.ContractDesc, concat(Employees.FirstName,' ',Employees.LastName) Name, SUM(WorkHours.HoursWorked) HoursWorked, BillingRates.Rate Rate, BillingRates.Rate * SUM(WorkHours.HoursWorked) Total_Charges, concat(Contacts.FirstName, ' ',Contacts.LastName), Contacts.AddrLine1 from master.dbo.Clients Clients left join master.dbo.Contracts Contracts on Clients.ClientID = Contracts.ClientID left join master.dbo.Projects Projects on Projects.ContractID = Contracts.ContractID left join master.dbo.EmployeesProjects EmployeesProjects on EmployeesProjects.ProjectID = Projects.ProjectID left join master.dbo.Employees Employees on Employees.EmpID = EmployeesProjects.EmpID left join master.dbo.WorkHours WorkHours on WorkHours.EmpID = Employees.EmpID left join master.dbo.BillingRates BillingRates on BillingRates.TitleID = Employees.TitleID and Employees.Level = BillingRates.Level left join master.dbo.ContractsContacts ContractsContacts on ContractsContacts.ContractID = Contracts.ContractID left join master.dbo.Contacts Contacts on Contacts.ContactID = ContractsContacts.ContactID left join master.dbo.ContactTypes ContactTypes on ContactTypes.ContactTypeID = Contacts.ContactTypeID and ContactTypes.ContactType = 'Billing' where WorkHours.WH_Month = 4 and WorkHours.WH_Year = 2013

GROUP BY Clients.CommonName, Contracts.ContractDesc, concat(Employees.FirstName,' ',Employees.LastName), BillingRates.Rate, concat(Contacts.FirstName, ' ',Contacts.LastName), Contacts.AddrLine1

3.) Benefit Tracking select concat(Employees.FirstName,' ',Employees.LastName,'- ',BenefitTypes.BenefitType), Countries.VacationDays benifits, count(BenefitsTaken.BT_Year) taken, Countries.VacationDays - count(BenefitsTaken.BT_Year) leftover

from master.dbo.Employees Employees left join master.dbo.Countries Countries on Employees.CountryID = Countries.CountryID

left join master.dbo.BenefitsTaken BenefitsTaken on Employees.EmpID = BenefitsTaken.EmpID and BenefitsTaken.BT_Year = 2013

inner join master.dbo.BenefitTypes BenefitTypes on BenefitTypes.BenefitTypeID = BenefitsTaken.BenefitTypeID and BenefitTypes.BenefitTypeID =1

group by concat(Employees.FirstName,' ',Employees.LastName,'- ',BenefitTypes.BenefitType), Countries.VacationDays, BenefitsTaken.EmpID

union select concat(Employees.FirstName,' ',Employees.LastName,'- ',BenefitTypes.BenefitType), Countries.VacationDays benifits, count(BenefitsTaken.BT_Year) taken, Countries.VacationDays - count(BenefitsTaken.BT_Year) leftover

from master.dbo.Employees Employees left join master.dbo.Countries Countries on Employees.CountryID = Countries.CountryID

left join master.dbo.BenefitsTaken BenefitsTaken on Employees.EmpID = BenefitsTaken.EmpID and BenefitsTaken.BT_Year = 2013

inner join master.dbo.BenefitTypes BenefitTypes on BenefitTypes.BenefitTypeID = BenefitsTaken.BenefitTypeID and BenefitTypes.BenefitTypeID =2

group by concat(Employees.FirstName,' ',Employees.LastName,'- ',BenefitTypes.BenefitType), Countries.VacationDays, BenefitsTaken.EmpID

4.) Management Exception Reporting
a.
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 =
WorkHours.EmpID

inner join (select Projects.ProjectID projid, sum(WorkHours.HoursWorked) hoursworked2
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 >
Countries.WeeklyHours

group by concat(Employees.FirstName,' ',Employees.LastName), Countries.CountryName, Countries.WeeklyHours, weeklyhours1.week1