I just need help with Part B. I have already created the tables and added the data but I am not sure how to go about creating the reports.

The employee table from the homework document does not mention to have the Region_ID Field. Would you like me to add it anyways?

Part A: Table Creation and Data Loading

Instructions: Create a new database in SQL Server and run the following CREATE TABLE commands. Note that you must run the CREATE TABLE statements in the order presented (and load the data in the order presented) to avoid conflicts resulting from foreign key constraints.

Additional instructions for materials to turn in for this phase of your project are included at the end of this specification document.

CREATE TABLE Regions

(RegionID int not null,

RegionAbbreviation varchar(4),

RegionName varchar(100),

CONSTRAINT PK_Regions PRIMARY KEY (RegionID))

CREATE TABLE Countries

(CountryID int not null,

CountryName varchar(50),

WeeklyHours int,

Holidays int,

VacationDays int,

RegionID int,

CONSTRAINT PK_Countries PRIMARY KEY (CountryID),

CONSTRAINT FK_CountriesRegions FOREIGN KEY (RegionID) References Regions)

CREATE TABLE EmployeeTitles

(TitleID int not null,

Title varchar(15),

CONSTRAINT PK_EmpTitles PRIMARY KEY (TitleID))

CREATE TABLE BillingRates

(TitleID int not null,

Level int not null,

Rate float,

CurrencyName varchar(5),

CONSTRAINT PK_BillingRates PRIMARY KEY (TitleID, Level),

CONSTRAINT FK_BillingRatesTitles FOREIGN KEY (TitleID) References EmployeeTitles)

CREATE TABLE Employees

(EmpID int not null,

FirstName varchar(30),

LastName varchar(30),

Email varchar(50),

Salary decimal(10,2),

TitleID int,

Level int,

SupervisorID int,

CountryID int,

CONSTRAINT PK_Employees PRIMARY KEY (EmpID),

CONSTRAINT FK_EmployeesCountries FOREIGN KEY (CountryID) References Countries,

CONSTRAINT FK_EmployeesEmpTitles FOREIGN KEY (TitleID) References EmployeeTitles,

CONSTRAINT FK_EmployeeSupervisors FOREIGN KEY (SupervisorID) References Employees)

CREATE TABLE ContactTypes

(ContactTypeID int not null,

ContactType varchar(30)

CONSTRAINT PK_ContactTypes PRIMARY KEY (ContactTypeID))

CREATE TABLE ContractTypes

(ContractTypeID int not null,

ContractType varchar(30)

CONSTRAINT PK_ContractTypes PRIMARY KEY (ContractTypeID))

CREATE TABLE BenefitTypes

(BenefitTypeID int not null,

BenefitType varchar(30)

CONSTRAINT PK_BenefitTypes PRIMARY KEY (BenefitTypeID))

CREATE TABLE Clients

(ClientID int not null,

LegalName varchar(50),

CommonName varchar(50),

AddrLine1 varchar(50),

AddrLine2 varchar(50),

City varchar(25),

State_Province varchar(25),

Zip varchar(9),

CountryID int,

CONSTRAINT PK_Clients PRIMARY KEY (ClientID),

CONSTRAINT FK_ClientsCountries FOREIGN KEY (CountryID) REFERENCES Countries)

CREATE TABLE Contacts

(ContactID int not null,

FirstName varchar(50),

LastName varchar(50),

AddrLine1 varchar(50),

AddrLine2 varchar(50),

City varchar(25),

State_Province varchar(25),

Zip varchar(9),

CountryID int,

ContactTypeID int,

CONSTRAINT PK_Contacts PRIMARY KEY (ContactID),

CONSTRAINT FK_ContactsCountries FOREIGN KEY (CountryID) REFERENCES Countries)

CREATE TABLE ContractTypes

(ContractTypeID int not null,

ContractTypeDesc varchar(50),

CONSTRAINT PK_ContractTypes PRIMARY KEY (ContractTypeID))

CREATE TABLE Contracts

(ContractID int not null,

ContractDesc varchar(100),

ClientID int,

ContractTypeID int,

CONSTRAINT PK_Contracts PRIMARY KEY (ContractID),

CONSTRAINT FK_ContractsClients FOREIGN KEY (ClientID) REFERENCES Clients,

CONSTRAINT FK_ContractsContractTypes FOREIGN KEY (ContractTypeID) REFERENCES ContractTypes)

CREATE TABLE ContractsContacts

(ContractID int not null,

ContactID int not null,

CONSTRAINT PK_ContractsContacts PRIMARY KEY (ContractID, ContactID),

CONSTRAINT FK_CC_Contracts FOREIGN KEY (ContractID) REFERENCES Contracts,

CONSTRAINT FK_CC_Contacts FOREIGN KEY (ContactID) REFERENCES Contacts)

CREATE TABLE Projects

(ProjectID int not null,

ProjectName varchar(50),

HourCapAmount decimal(10,2),

ProjectManagerID int,

ContractID int,

CONSTRAINT PK_Projects PRIMARY KEY (ProjectID),

CONSTRAINT FK_ProjectsEmployees FOREIGN KEY (ProjectManagerID) REFERENCES Employees,

CONSTRAINT FK_ProjectsContracts FOREIGN KEY (ContractID) REFERENCES Contracts)

CREATE TABLE EmployeesProjects

(EmpID int not null,

ProjectID int not null,

StartDate smalldatetime,

EndDate smalldatetime,

CONSTRAINT PK_EmployeesProjects PRIMARY KEY (EmpID, ProjectID),

CONSTRAINT FK_EP_Employees FOREIGN KEY (EmpID) REFERENCES Employees,

CONSTRAINT FK_EP_Projects FOREIGN KEY (ProjectID) REFERENCES Projects)

CREATE TABLE Timesheets

(TimesheetID int not null,

SupervisorApproveDate smalldatetime,

CONSTRAINT PK_Timesheets PRIMARY KEY (TimesheetID))

CREATE TABLE WorkHours

(EmpID int not null,

ProjectID int not null,

WH_Day int not null,

WH_Month int not null,

WH_Year int not null,

HoursWorked float,

TimesheetID int,

CONSTRAINT PK_WorkHours PRIMARY KEY (EmpID, ProjectID, WH_Day, WH_Month, WH_Year),

CONSTRAINT FK_WorkHoursEmployees FOREIGN KEY (EmpID) REFERENCES Employees,

CONSTRAINT FK_WorkHoursProjects FOREIGN KEY (ProjectID) REFERENCES Projects,

CONSTRAINT FK_WorkHoursTimesheets FOREIGN KEY (TimesheetID) REFERENCES Timesheets)

CREATE TABLE BenefitsTaken

(EmpID int not null,

BenefitTypeID int not null,

BT_Day int not null,

BT_Month int not null,

BT_Year int not null,

HoursTaken float,

TimesheetID int,

CONSTRAINT PK_BenefitsTaken PRIMARY KEY (EmpID, BenefitTypeID, BT_Day, BT_Month, BT_Year),

CONSTRAINT FK_BenefitsTakenEmployees FOREIGN KEY (EmpID) REFERENCES Employees,

CONSTRAINT FK_BenefitsTakenBenefitTypes FOREIGN KEY (BenefitTypeID) REFERENCES BenefitTypes,

CONSTRAINT FK_BenefitsTakenTimesheets FOREIGN KEY (TimesheetID) REFERENCES Timesheets)

Part B: Reports

Human Resources:

The HR department requires a list of all the employees who are employed by CMS. This information should be organized as follows:

Region

Country

Employee name (Last, First)

Title + Level (e.g. Consultant - 1)

Salary (in USD)

*Sort data in ascending order first by region, then by country, then by employee last name, then by title, and then by salary.

Instructions:

For this assignment, write the query that produces the results as described above.

Invoicing

Accounting requires information to produce invoices. For each client, CMSs invoicing controller must know the following information as of the last day of each month:

Client name

Contract name(s)

Project(s)

Employees who logged hours to a project from the first day of the current month until the last day of the current month

Total number of hours logged for each employee during the month

Employee rate

Total charges per employee (i.e. employee rate x employee hours worked)

Billing contact(s) (name, address) for each contract

*Sort data in ascending order first by client, then by project, and then by employee.

Instructions:

All of this information should be produced using a single query that can serve as the basis for a report. Do not use views or stored procedures in conjunction with your query.

For this assignment, you will write your query for only the month of April 2013. You may hardcode the month number in your query. In the real world, you would likely run this report for the current month, in which case you would want to use the getdate() function to retrieve the current date. Conversely, you might produce this query as a stored procedure that takes a given month as an argument and returns a resultset. For your assignment, however, just assume this report will be run for April 2013 and hardcode this date in your query to produce the results.

Benefit Tracking

The HR department requires a report that provides information on benefit information. Assume a calendar year whereby new benefit allotments are granted as of January 1 and must be used by December 31 of same year. No carryover benefits are allowed.

Number of benefits days allotted to each employee

Number of benefit days taken year-to-date

Number of benefit days remaining in the calendar year

Number of holidays allotted to each employee

Number of holidays taken year-to-date

Number of holidays remaining in the calendar year

*Data must be sorted in ascending order by employee last name.

Instructions:

For this assignment, write a query that produces the results described above. Assume that you are running the report for the 2013 calendar year. As in the previous report, in the real world, you would likely use the getdate() function to determine the current date and run the report from the beginning of the current year until the present time. For this assignment, however, you may hardcode the year 2013 in your query and retrieve all of the data for that year.

Management Exception Reporting

Management must keep track of employees whose combined hours have exceeded the maximum allowed hours on projects. This report must be run before invoicing occurs in order to prevent billing in excess of contractual amounts. Show only projects whose cap amounts have been exceeded.

Project name

Maximum allowed hours per project

Total hours worked on project

Overage (the difference between the cap and actual hours)

*Sort data by project name.

In a separate query, show the details for the projects whose cap amounts have been exceeded:

Project name

Employees who worked on project

Total hours worked on project per employee

*Sort data by project name and then by employees who worked on the project

Instructions:

For this assignment, write a query for 4(a) and a separate query for 4(b). The results must reflect the requirements described above.

Payroll

The payroll department requires a report of employees who are logging more hours per week than they are legally required to work per country stipulations. These employees are paid overtime wages for hours worked in excess of weekly stipulated hours.

Employee name

Employee country

Weekly Hours per employee per country

Hours logged by employee in current week