Table Expressions part 2

use MetroAlt
--the correlated subquery from the assignment
Select PositionKey, EmployeeKey, EmployeeHourlyPayRate
From EmployeePosition ep1
where EmployeeHourlyPayRate =
(Select Max(EmployeeHourlyPayRate) 
From EmployeePosition ep2
Where ep1.PositionKey=ep2.PositionKey)

--just a subquery that select the max
--for one position
Select employeeKey, PositionKey, EmployeeHourlypayRate from EmployeePosition
Where  Positionkey=1 
And EmployeeHourlypayRate = 
(Select Max(EmployeeHourlyPayrate) from EmployeePosition where PositionKey = 1)

--Table Expressions
--views

Use communityAssist
GO --seperates batches

--a view is a stored query that offers a
--"view" of the data
Create view vw_HREmployees
AS
Select PersonLastName [Last Name],
PersonFirstname [First Name],
PersonUserName Email,
[Street],
[Apartment],
[State],
[Zip],
[ContactInfo] Phone,
[ContactTypeName] [Contact Type],[EmployeeHireDate]  [Hire Date],[EmployeeSSNumber] SSNumber,
[EmployeeMonthlySalary] [Monthly Salary]
From person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join PersonContact pc
on pc.PersonKey =p.PersonKey
inner join Employee e
on e.PersonKey=p.PersonKey
inner join ContactType ct
on ct.ContactTypeKey = pc.ContactTypeKey


Go
--using the view
--order by is not allowed in a view but
--you can use it in the select when calling
--the view
Select * from vw_HREmployees
order by [Last Name]

go
--changing the view to add schemabinding
--schema binding prevents a user from
--changing the underlying tables on which
--the view depends
Alter view vw_HREmployees with schemabinding
AS
Select PersonLastName [Last Name],
PersonFirstname [First Name],
PersonUserName Email,
[Street],
[Apartment],
[State],
[Zip],
[ContactInfo] Phone,
[ContactTypeName] [Contact Type],[EmployeeHireDate]  [Hire Date],[EmployeeSSNumber] SSNumber,
[EmployeeMonthlySalary] [Monthly Salary]
From dbo.person p
inner join dbo.PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join dbo.PersonContact pc
on pc.PersonKey =p.PersonKey
inner join dbo.Employee e
on e.PersonKey=p.PersonKey
inner join dbo.ContactType ct
on ct.ContactTypeKey = pc.ContactTypeKey

Begin tran --begin transaction
--this attempted alteration results in an error
--becase the view depends on this table and
--column
Alter Table Employee
Drop Column EmployeeSSNumber
Rollback tran --not necessary because nothing
--happened but need to end the tran

go
--create a schema (ownership)
Create Schema EmployeeSchema

--fully qualified path
--Server.Database.Schema.Table

--create a view owned by the schema
Create view EmployeeSchema.vw_HireDates
As
Select * From Employee
go

--create a table valued function
Create function fx_Employee
(@EmployeeKey int) --parameter
returns table --return type
As
Return
Select GrantKey, PersonKey, 
GrantNeedExplanation, GrantReviewDate,
GrantApprovalStatus, GrantAllocation
From ServiceGrant
Where EmployeeKey = @EmployeeKey
Go
--using the function 4 is an employeeKey
Select * from dbo.fx_Employee(4)

--cross apply
Select distinct a.ServiceKey, c.GrantAllocation
From dbo.ServiceGrant as a
Cross Apply
(Select serviceKey, grantAllocation, GrantKey
From ServiceGrant b
Where b.ServiceKey=a.ServiceKey
Order by GrantAllocation desc, serviceKey desc
Offset 0 rows Fetch First 3 rows only) as c