rlswisher (2/28/2012)

---

For 2 and 3, I made an assumption for your field to determine if Exempt or Not, Employee State of Residence, Employee Hire Date, and Employee Birth Date

--1.Choose a Job Description and increase by 10% all employees’ salaries that have the selected Job Description (not Job Title).

UPDATE Employee

SET salary = salary + (salary * (10/100))

FROM Employee

INNER JOIN job_title

ON Employee.job_desc=job_title.job_desc

WHERE job_desc = 'Obtains or prepares food items requested by customers in retail food store'

--2. Decrease all Exempt employees’ salaries from a certain State by 15%.

UPDATE Employee

SET salary = salary - (salary * (15/100))

FROM Employee

WHERE Exempt = 'Yes' --Change this to reflect how you identify Exempt vs Non-Exempt

And Employee_State = 'WI' --insert your state code here

--3. Increase all Non-Exempt employees’ salaries hired more than 3 years ago that are less than 30 years of Age, by 5%.

UPDATE Employee

SET salary = salary + (salary * (5/100))

FROM Employee

WHERE Exempt = 'No' --Change this to reflect how you identify Exempt vs Non-Exempt

And DATEDIFF(dd,Date_Hired,GETDATE()) > 1095 --365*3 = 1095, which be be the min days in 3 years, if a leap year it would be 1096.

And (CASE WHEN (DATEADD(year,DATEDIFF(year, Birth_Date ,GETDATE()) , Birth_Date) > GETDATE())

THEN DATEDIFF(year, Birth_Date ,GETDATE()) -1

ELSE DATEDIFF(year, Birth_Date ,GETDATE()) END) < 30