SQL problem

USE PR

IF OBJECT_ID('Emp') IS NOT NULL

DROP VIEW Emp;

GO

CREATE VIEW Emp

AS

SELECT EmpID, FirstName + ' ' + LastName AS EmpName,

HireDate, IIF(Spouse=1, 'Yes', 'No') AS Married, Dependants

FROM EmpData

WHERE TermDate IS NULL

USE PR

IF OBJECT_ID('PayRate') IS NOT NULL

DROP VIEW PayRate;

GO

CREATE VIEW PayRate

AS

SELECT EmpID, PayRate, IIF(Salaried=1, 'Yes', 'No') AS Salaried,

StartDate AS PayStartDate

FROM Work

WHERE EndDate IS NULL

SELECT * FROM Emp

SELECT * FROM PayRate

USE PR

IF OBJECT_ID('CurrentEmps') IS NOT NULL

DROP VIEW CurrentEmps;

GO

CREATE VIEW CurrentEmps

AS

SELECT EmpName, PayRate, Salaried, Married, Dependants,

CONVERT(varchar, HireDate, 107) AS HireDate

FROM Emp JOIN PayRate

ON Emp.EmpID = PayRate.EmpID

USE PR

SELECT * FROM CurrentEmps

USE PR

IF OBJECT_ID('EmpInsPremiums') IS NOT NULL

DROP VIEW EmpInsPremiums;

GO

CREATE VIEW EmpInsPremiums

AS

SELECT EmpID, LastName, FirstName + ' ' + LastName AS EmpName, BaseCost,

IIF(Spouse=1, SpouseCost, 0) AS SpouseCost,

IIF(Dependants>0, Dependants * DepCost, 0) AS DependantCost, DentalCost, PlanName

FROM Benefits B JOIN EmpData E

ON B.BenPlanID = E.BenPlanID

WHERE TermDate IS NULL

USE PR

SELECT * FROM EmpInsPremiums

USE PR

SELECT EmpName, BaseCost+SpouseCost+DependantCost+DentalCost AS TotInsPrem,

PayRate, Dept, Title, Grade, PlanName

FROM EmpInsPremiums EIS JOIN Work W

ON EIS.EmpID = W.EmpID

JOIN Department D ON w.DeptID = D.DeptID

WHERE EndDate IS NULL

ORDER BY TotInsPrem DESC

USE PR

IF OBJECT_ID('Work1') IS NOT NULL

DROP VIEW Work1;

GO

CREATE VIEW Work1

AS

SELECT WorkID, PayRate

FROM Work

WHERE EndDate IS NULL

ORDER BY PayRate DESC

USE PR

IF OBJECT_ID('Work1') IS NOT NULL

DROP VIEW Work1;

GO

CREATE VIEW Work1

AS

SELECT TOP 100 PERCENT WorkID, PayRate

FROM Work

WHERE EndDate IS NULL

ORDER BY PayRate DESC

SELECT * FROM Work1

USE PR

IF OBJECT_ID('AllEmployees') IS NOT NULL

DROP VIEW AllEmployees;

GO

CREATE VIEW AllEmployees

([First], [Last], WhenHired, WhenFired)

AS

SELECT FirstName, LastName, HireDate, TermDate

FROM EmpData

SELECT * FROM AllEmployees

USE PR

IF OBJECT_ID('HoursSummary') IS NOT NULL

DROP VIEW HoursSummary;

GO

CREATE VIEW HoursSummary

(PPID, SWH, SHH, SVH, SPH, SSH, SOTH, SRH)

AS

SELECT PPID, SUM(WorkHours), SUM(HolHours),

SUM(VacHours), SUM(PersHours), SUM(SickHours),

SUM(IIF(WorkHours>40, WorkHours-40, 0)),

SUM(WorkHours)-SUM(IIF(WorkHours>40, WorkHours-40, 0))

FROM Hours

GROUP BY PPID WITH ROLLUP

SELECT * FROM HoursSummary

USE PR

IF OBJECT_ID('HoursSummary2') IS NOT NULL

DROP VIEW HoursSummary2;

GO

CREATE VIEW HoursSummary2 WITH ENCRYPTION

AS

SELECT PPID, SUM(WorkHours) AS SumWH, SUM(HolHours) AS SumHH,

SUM(VacHours) AS SumVH, SUM(PersHours) AS SumPH, SUM(SickHours) AS SumSH,

SUM(IIF(WorkHours>40, WorkHours-40, 0)) AS SumOT,

SUM(WorkHours)-SUM(IIF(WorkHours>40, WorkHours-40, 0)) AS SumRH

FROM Hours

GROUP BY PPID WITH ROLLUP

USE PR

IF OBJECT_ID('HoursTest') IS NOT NULL

DROP TABLE HoursTest;

GO

SELECT *

INTO HoursTest

FROM Hours

GO

IF OBJECT_ID('WorkTest') IS NOT NULL

DROP TABLE WorkTest;

GO

SELECT *

INTO WorkTest

FROM Work

GO

USE PR

IF OBJECT_ID('HoursTest1') IS NOT NULL

DROP VIEW HoursTest1;

GO

CREATE VIEW HoursTest1

AS

SELECT * FROM HoursTest

GO

IF OBJECT_ID('WorkTest1') IS NOT NULL

DROP VIEW WorkTest1;

GO

CREATE VIEW WorkTest1

AS

SELECT * FROM WorkTest

USE PR

SELECT * FROM HoursTest1

WHERE PPID = 24

UPDATE HoursTest1

SET HolHours = 8,

WorkHours = WorkHours - 8

WHERE PPID = 24 AND HolHours <> 8

GO

SELECT * FROM HoursTest1

WHERE PPID = 24

USE PR

IF OBJECT_ID('EmpDataTest') IS NOT NULL

DROP TABLE EmpDataTest;

GO

SELECT *

INTO EmpDataTest

FROM EmpData

GO

IF OBJECT_ID('BenefitsTest') IS NOT NULL

DROP TABLE BenefitsTest;

GO

SELECT *

INTO BenefitsTest

FROM Benefits

GO

IF OBJECT_ID('DepartmentTest') IS NOT NULL

DROP TABLE DepartmentTest;

GO

SELECT *

INTO DepartmentTest

FROM Department

GO

IF OBJECT_ID('PayPeriodTest') IS NOT NULL

DROP TABLE PayPeriodTest;

GO

SELECT *

INTO PayPeriodTest

FROM PayPeriod

GO

USE PR

IF OBJECT_ID('EmpDataTest1') IS NOT NULL

DROP VIEW EmpDataTest1;

GO

CREATE VIEW EmpDataTest1

AS

SELECT * FROM EmpDataTest

GO

IF OBJECT_ID('BenefitsTest1') IS NOT NULL

DROP VIEW BenefitsTest1;

GO

CREATE VIEW BenefitsTest1

AS

SELECT * FROM BenefitsTest

GO

IF OBJECT_ID('DepartmentTest1') IS NOT NULL

DROP VIEW DepartmentTest1;

GO

CREATE VIEW DepartmentTest1

AS

SELECT * FROM DepartmentTest

GO

IF OBJECT_ID('PayPeriodTest1') IS NOT NULL

DROP VIEW PayPeriodTest1;

GO

CREATE VIEW PayPeriodTest1

AS

SELECT * FROM PayPeriodTest

GO

USE PR

SELECT * FROM PayPeriodTest1

ORDER BY PPID DESC

INSERT INTO PayPeriodTest1

(PerFrom, PerThru)

VALUES('7/13/2014', '7/19/2014')

SELECT * FROM PayPeriodTest1

ORDER BY PPID DESC

USE PR

INSERT INTO EmpDataTest1

VALUES ('William', 'Meyers', GETDATE(), 0, 0, 1, NULL, 9)

SELECT * FROM EmpDataTest1

ORDER BY EmpID DESC

USE PR

INSERT INTO WorkTest1

(DeptID, EmpID, COLA, EndDate, StartDate, Salaried, PayRate)

VALUES(17, 81, 0, NULL, GETDATE(), 0, 20.16)

SELECT * FROM WorkTest1

ORDER BY WorkID DESC

USE PR

IF OBJECT_ID('NewEmpData') IS NOT NULL

DROP VIEW NewEmpData;

GO

CREATE VIEW NewEmpData

AS SELECT E.EmpID, FirstName + ' ' + LastName AS EmpName,

PayRate, Dept, Title + ' Grade ' + CONVERT(varchar, Grade, 1) AS Position

FROM BenefitsTest1 B JOIN EmpDataTest1 E

ON B.BenPlanID = E.BenPlanID

JOIN WorkTest1 W ON e.EmpID = W.EmpID

JOIN DepartmentTest1 D ON W.DeptID = D.DeptID

WHERE B.BenPlanID IN (SELECT BenPlanID

FROM BenefitsTest1

WHERE PlanName = '90 Day Probation Period')

USE PR

GO

ALTER VIEW NewEmpData

AS

SELECT E.EmpID AS EID, FirstName AS First, PayRate AS PR, Dept AS D

FROM BenefitsTest1 B JOIN EmpDataTest1 E

ON B.BenPlanID = E.BenPlanID

JOIN WorkTest1 W ON e.EmpID = W.EmpID

JOIN DepartmentTest1 D ON W.DeptID = D.DeptID

WHERE B.BenPlanID IN (SELECT BenPlanID

FROM BenefitsTest1

WHERE PlanName = '90 Day Probation Period')