Phase II CMS Project Thorn A

п»їBMIS 325 Phase II

Part A: Table Creation and Info Loading

Part B: Reviews

1 . ) Human Resources:

Select Regions. RegionName,

Countries. CountryName,

concat(Employees. LastName, ', ', Employees. FirstName) EmployeeName,

concat(EmployeeTitles. Name, ' -- ', Employees. Level) Name,

concat(Employees. Salary, '(USD)') Salary

coming from master. dbo. Employees Staff

left sign up for master. dbo. Countries Countries

on Workers. CountryID sama dengan Countries. CountryID

left join master. dbo. EmployeeTitles EmployeeTitles

on EmployeeTitles. TitleID = Employees. TitleID

left sign up for 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 Consumers. CommonName,

Deals. ContractDesc,

concat(Employees. FirstName, ' ', Personnel. LastName) Brand,

SUM(WorkHours. HoursWorked) HoursWorked,

BillingRates. Rate Charge,

BillingRates. Rate * SUM(WorkHours. HoursWorked) Total_Charges,

concat(Contacts. FirstName, ' ', Contacts. LastName),

Contacts. AddrLine1

from grasp. dbo. Consumers Clients

left join expert. dbo. Agreements Contracts

about Clients. ClientID = Contracts. ClientID

left join learn. dbo. Tasks Projects

in Projects. ContractID = Deals. ContractID

remaining join expert. dbo. EmployeesProjects EmployeesProjects

upon EmployeesProjects. ProjectID = Jobs. ProjectID

still left join grasp. dbo. Staff Employees

about Employees. EmpID = EmployeesProjects. EmpID

remaining join master. dbo. WorkHours WorkHours

on WorkHours. EmpID = Workers. EmpID

left join learn. dbo. BillingRates BillingRates

on BillingRates. TitleID = Workers. TitleID

and Employees. Level = BillingRates. Level

remaining join expert. dbo. ContractsContacts ContractsContacts

about ContractsContacts. ContractID = Legal agreements. ContractID

remaining join master. dbo. Associates Contacts

in Contacts. ContactID = ContractsContacts. ContactID

left join grasp. dbo. ContactTypes ContactTypes

upon ContactTypes. ContactTypeID = Contacts. ContactTypeID

and ContactTypes. ContactType = 'Billing'

where WorkHours. WH_Month sama dengan 4

and WorkHours. WH_Year = 2013

GROUP SIMPLY BY Clients. CommonName,

Contracts. ContractDesc,

concat(Employees. FirstName, ' ', Employees. LastName),

BillingRates. Charge,

concat(Contacts. FirstName, ' ', Contacts. LastName),

Contacts. AddrLine1

3. ) Benefit Checking

select

concat(Employees. FirstName, ' ', Employees. LastName, '- ', BenefitTypes. BenefitType),

Countries. VacationDays benifits,

count(BenefitsTaken. BT_Year) considered,

Countries. VacationDays -- count(BenefitsTaken. BT_Year) leftover

from master. dbo. Employees Workers

left become a member of master. dbo. Countries Countries

on Staff. CountryID sama dengan Countries. CountryID

left join master. dbo. BenefitsTaken BenefitsTaken

on Staff. EmpID sama dengan BenefitsTaken. EmpID

and BenefitsTaken. BT_Year sama dengan 2013

inner join expert. dbo. BenefitTypes BenefitTypes

about BenefitTypes. BenefitTypeID = BenefitsTaken. BenefitTypeID

and BenefitTypes. BenefitTypeID =1

group by concat(Employees. FirstName, ' ', Employees. LastName, '- ', BenefitTypes. BenefitType),

Countries. VacationDays,

BenefitsTaken. EmpID

union

choose concat(Employees. FirstName, ' ', Employees. LastName, '- ', BenefitTypes. BenefitType),

Countries. VacationDays benifits,

count(BenefitsTaken. BT_Year) taken,

Countries. VacationDays - count(BenefitsTaken. BT_Year) leftover

coming from master. dbo. Employees Staff

left join master. dbo. Countries Countries

on Personnel. CountryID sama dengan Countries. CountryID

left join master. dbo. BenefitsTaken BenefitsTaken

on Employees. EmpID = BenefitsTaken. EmpID

and BenefitsTaken. BT_Year = 2013

interior join master. dbo. BenefitTypes BenefitTypes

about BenefitTypes. BenefitTypeID = BenefitsTaken. BenefitTypeID

and BenefitTypes. BenefitTypeID =2

group by concat(Employees. FirstName, ' ', Workers. LastName, '-...

 Essay about Cultural Examination

Essay about Cultural Examination

Global Business Ethnical Analysis: Germany July six, 2012 Freedom University Subjective The Indian population in america as increased over the years. You will discover signs they…...

Read..
 Gibraltar Article

Gibraltar Article

1) Introduction Gibraltar is a small peninsula on the southern coast of Spain and with its 35, 000 occupants and part of 2 . 6 square kilometers (6. several…...

Read..

Most Common Behavioral Disorders of Childhood Dissertation

ADHD Research Pitch Antonio Herron EDU626: Analysis Design and Methodology (MRD1221B) Instructor: Rita Daniel Come july 1st 3, 2012 Abstract ADHD, or attention-deficit hyperactivity…...

Read..

the years of me Dissertation

п»ї Part 1: Scenario 1)What are you going to buy? (Provide a detailed explanation of the key purchase) A used 2010 Toyota Tacoma pickup truck 2)Why…...

Read..
 Essay on H. They would. Holmes

Essay on H. They would. Holmes

DR . L. H. Sherlock holmes, One of Americas first Dramon Killers. Tommy Smith Legal Justice Example Instructor: Dennis Stephens 3/24/11 Bibliography Borowski, John (November…...

Read..

Foundations of Psychology Composition

п»їOver the course of history, people have been awed by the power and complexity from the human habit, but it wasn't until the nineteenth century that psychology started…...

Read..