ComputerScienceExpert
$18/per page/
I need help with the following- I need all parts to be completed -
Part A: Table Creation and Data Loading (PLEASE SEE ATTACHMENT FOR DATA TABLE)
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.
Part B: Reports
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.
Accounting requires information to produce invoices. For each client, CMS’s 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.
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 ReportingManagement 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.
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 |
Instructions:
For this assignment, produce a query that determines employees who have incurred overtime during April 2013.
Phase II Deliverables:
For example, SELECT * from Clients
CSIS 325 CMS PROJECT – PHASE II INSTRUCTIONS
In this phase, you will create tables based upon the ERD and SQL code below. You will then
populate each table with the data presented below. Finally, you will create queries that will be
used to support reports for Accounting and Management. You will not actually create the reports
in a GUI environment, only the queries that will serve as the basis for the reports. Screenshots
are required for a grade to be given. One screenshot is not the idea; however, multiple
screenshots along the way is the goal.
Background:
The following ERD will be used as the basis for this Phase. EndDate StartDate AssignedTo Clients Projects
ProjectID
ProjectName
HourCapAmount Signs Contracts
Includes ContractID
ContractDesc ClientID
LegalName
CommonName
AddrLine1
AddrLine2
City
State
Zip Supports Employees EmpIsA
Charges Manages EmpID
FirstName
LastName
OfficeID
Email
Salary
TitleID
Level Supervises
ContractTypes
WorkHours
Logs Day
Month
Year
HoursWorked EmployeeTitles
TitleID
Title Has Records ContractTypeID
ContractTypeName
Contacts
Summarizes TimeSheets
TimeSheetID
SupApproveDate LivesIn
Takes Bills BenefitsTaken
Day
Month
Year
HoursTaken BillingRates
Rate
Currency Totals ContactID
ContactFirstName
ContactLastName
ContactAddrLine1
ContactAddrLine2
ContactCity
ContactState
ContactZip IsATypeOf Regions
IsIn RegionID
RegionName HasType BenefitTypes
BenefitTypeID
BenefitTypeName ContactTypes Countries
CountryID
CountryName
WeeklyHours
Holidays
VacationDays WorksIn ContactTypeID
ContactTypeName LocatedIn Page 1 of 17 CSIS 325 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) Page 2 of 17 CSIS 325 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) Page 3 of 17 CSIS 325 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), Page 4 of 17 CSIS 325 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, Page 5 of 17 CSIS 325 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)
Data Section
The following information is currently maintained in various spreadsheets throughout CMS.
Data from these spreadsheets must be uploaded into your newly created tables before the
database can be considered operational.
Regions
ID
1
2
3
4 Abbr.
NAR
CALA
APAC
EMEA Region Name
North America
Central and Latin America
Asia and Pacific
Europe, Middle East, and Africa Countries
ID
1
2
NAR
3
4
5
6
7
8
9 Country Name
United States
Canada Weekly Hours
40
40 Holidays
11
12 Vacation Days
10
15 Region
NAR United Kingdom
France
Ireland
Italy
Thailand
Singapore
Panama 38
38
38
35
40
40
40 10
14
10
9
17
17
12 10
10
15
20
20
21
15 EMEA
EMEA
EMEA
EMEA
APAC
APAC
CALA Benefit Types
ID
1
2
3
4
5
6 Benefit Type Name
Vacation
Holiday
Jury Duty
Maternity Leave
Paternity Leave
Military Duty Page 6 of 17 CSIS 325 Contact Types
ID
1
2
3 Contact Type Name
Systems Engineer
Sales
Billing Contract Types
ID
1
2
3
4 Contract Type Name
Maintenance
Fixed Price
License
Time and Materials Clients
ID
1
2
3
4
5 Legal Name
BMA
FT
IBC
MTM
BT Common
British Mobile
France Mobile
IBC
MTM
Britain Tele Address1
Address2 City State Zip
130 Wake Dr.
Wake NC 24539
123 East St.
Suite #2
Paris
45678
456 Main
Johor
78945
6789 First St.
Mead GA 45678
98769 Park St. Level 3 London
48695 Country
US
France
Singapore
US
UK Contracts
ID
1
2
3
4
5
6 ContractDesc
Work Order 1
Work Order 1
Work Order 1
Work Order 2
Work Order 1
Work Order 2 Contract Type
Maint
T&M
Fixed Price
Maint
Fixed Price
T&M Client
FT
BT
IBC
IBC
MTM
FT Contacts
ID
1
2
3
4
5
6
7
8 First Last
Bugg Bunny
Elmer Fudd
Daffy Duck
Darth Vader
Luke
Sky
Princess Lea
John
Doe
Jane
Doe Addr1
Addr2 City
123 Looney
NoWhere
789 Park Pl.
Apt 3 Skyville
45678 One St.
Norwood
456 Two St.
Towns
#4 Tatooine
Paris
723 Coruscant
Rome
987 Main St.
Paris
7658 Oak Ln.
Crue State
AK
NM VA Zip
45678
45678
45678
47896
45678
45678
78945
45678 Country Type
US
SysEng
US
Billing
UK
Sales
UK
Sales
France Billing
Italy SysEng
France SysEng
US
SysEng Page 7 of 17 CSIS 325 Contracts’ Contacts
Contract
Work Order 1
Work Order 1
Work Order 2
Work Order 1
Work Order2
Work Order 2
Work Order 1 Client
BT
FT
FT
IBC
IBC
IBC
MTM Contact Name
Daffy Duck
John Doe, Jane Doe, Princess Lea
Elmer Fudd
Buggs Bunny
Luke Sky
Darth Vader
Daffy Duck Employee Titles
ID
1
2
3 Title
Consultant
Analyst
Director Billing Rates
TitleID
1
1
1
2
2
2
3
3
3 Level
1
2
3
1
2
3
1
2
3 Rate
150.00
200.00
300.00
50.00
100.00
150.00
250.00
350.00
450.00 Currency
USD
USD
USD
USD
USD
USD
USD
USD
USD CountryID Email Salary Title msmith@cms.com
mjones@cms.com
lrice@cms.com
jrich@cms.com
jDoe@cms.com
ppride@cms.com
epotter@cms.com
pdavis@cms.com 45000
94000
65000
74000
40000
60000
81000
103000 Consultant 1
Director 1
Consultant 2
Consultant 3
Analyst 1
Analyst 2
Consultant 3
Director 2 Employees
ID
1
2
3
4
5
6
7
8 First
LevelID
Matthew
Mark
Luke
John
James
Peter
Eric
Paul Last
Smith
Jones
Rice
Rich
Doe
Pride
Potter
Davis 1
1
4
5
6
3
3
1 Page 8 of 17 CSIS 325 Page 9 of 17 CSIS 325 Projects
ID
1
2
3
4
5
6
7
8
9 Project Name
HourCapAmount
IBC – India
120
FT-Maint
100
BT – WO 1 Time
270
BT – WO1 Materials
IBC - WO1
IBC – WO2
MTM – WO1
FT – WO2 Time
500
FT –WO2 Materials ProjectManager
Davis
Doe
Rich
Rich
Davis
Davis
Pride
Doe
Doe Contracts
Work Order 2
Work Order 2
Work Order1
Work Order1
Work Order 1
Work Order 2
Work Order 1
Work Order 2
Work Order 2 Client
IBC
FT
BT
BT
IBC
IBC
MTM
FT
FT Project-Employee Assignments
Employee
Doe
Doe
Doe
Smith
Jones
Rice Project
IBC-India
IBC - WO1
BT – WO1 Materials
FT-Maint
FT-Maint
MTM – WO1 StartDate
1/1/2013
5/7/2013
2/1/2013
2/1/2013
3/1/2013
1/1/2013 EndDate Project
IBC-India
IBC-India
IBC-India
IBC-India
IBC-India
IBC-India
IBC-India
IBC-India
IBC-India
IBC-India
IBC - WO1
IBC - WO1
IBC - WO1
IBC-India
IBC-India
IBC-India
IBC-WO1
IBC-India
IBC-India
IBC-India
IBC-India Month
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4 HoursWorked
8
8
8
8
8
8
8
8
8
4
4
4
4
8
8
5
3
8
8
8
8 4/30/2013 Work Hours
Employee
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe Day
2
3
4
5
6
9
10
11
12
13
13
16
16
17
18
19
19
20
23
24
26 Year
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013 TimeSheet
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
Page 10 of 17 CSIS 325 Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Doe
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones IBC-India
IBC-India
IBC-WO1
IBC-WO1
IBC-WO1
IBC-WO1
IBC-India
IBC-WO1
IBC-WO1
IBC-WO1
IBC-WO1
IBC-India
IBC-WO1
IBC-WO1
IBC-WO1
IBC-WO1
IBC-India
IBC-WO1
IBC-WO1
IBC-WO1
IBC-India
IBC-WO1
IBC-WO1
IBC-WO1
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint 27
30
1
2
3
4
7
8
9
10
11
14
15
16
17
18
21
22
23
24
28
29
30
31
2
3
4
5
6
9
10
11
12
13
16
17
18
19
20
23
24
26
27
30
1
2 4
4
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
5
5 2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013 8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
15
14
8
8
10
8
8
8
8
8
8
8
8 1
1
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
4
4 Page 11 of 17 CSIS 325 Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Smith FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint
FT-Maint 3
4
7
8
9
10
11
14
15
16
17
18
21
22
28
29
30
31
2
3
4
5
9
10
11
12
16
17
18
19
20
23
24
26
27
30
1
2
3
4
7
8
9
10
11
14 5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
5
5
5
5
5
5
5
5
5
5 2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013 8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
14
8
8
10
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8 4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
6
6
6
6
6
6
6
6
6
6 Page 12 of 17 CSIS 325 Smith
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice
Rice FT-Maint
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1
MTM – WO1 15
2
3
4
5
6
9
10
11
12
16
17
18
19
20
23
24
26
27
30
1
2
3
4
7
8
9
10
11
14
15 5
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
4
5
5
5
5
5
5
5
5
5
5
5 2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013 Year
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013
2013 BenefitType
Holiday
Holiday
Holiday
Vacation
Vacation
Holiday
Vacation
Holiday
Holiday
Holiday
Holiday 8
8
8
8
8
8
8
8
8
8
14
8
8
10
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8
8 6
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
8
8
8
8
8
8
8
8
8
8
8 Benefits Taken
Employee
Doe
Doe
Jones
Jones
Jones
Jones
Smith
Smith
Smith
Rice
Rice Day
25
25
25
23
24
25
6
25
25
25
25 Month
4
5
4
5
5
5
4
4
5
4
5 TimeSheet
1
2
3
3
4
4
5
5
6
7
8 Page 13 of 17 CSIS 325 Timesheets
ID
1
2
3
4
5
6
7
8 SupervisorApproveDate
4/30/2013
5/31/2013
4/30/2013
5/31/2013
4/30/2013
5/31/2013
4/30/2013
5/31/2013 Part B: Reports
1. 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.
2. Invoicing
Accounting requires information to produce invoices. For each client, CMS’s 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 Page 14 of 17 CSIS 325 *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.
3. 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. Page 15 of 17 CSIS 325 4. Management Exception Reporting
a. 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.
b. 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.
5. 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 Page 16 of 17 CSIS 325 Instructions:
For this assignment, produce a query that determines employees who have incurred overtime
during April 2013.
Phase II Deliverables:
1. In a Word document, take screen shots of the data in each of your tables using basic
SELECT statements.
For example, SELECT * from Clients
2. Write queries for each of the reports above. In the same Word document, include
screenshots of your queries from SQL Server Express (or SQL Server). Below EACH
query, include (via screen shots) the results of each query.
3. Name your Word document as follows: “Phase II CMS Project – your last name followed
by your first initial.”
Using the link provided in Blackboard, upload the CMS Project – Phase II by 11:59 p.m. (ET) on
Friday of Module/Week 8. Page 17 of 17