Table Expressions

--Table Expressions
Use Community_Assist

--Table expressions are Subqueries in the from clause
Select PersonKey, [Last], [First], City 
From 
(Select p.PersonKey, 
PersonLastName [Last],
PersonFirstName [First],
PersonAddressCity City
From Person p
inner join PersonAddress pa
on p.PersonKey =pa.PersonKey
Where PersonAddressCity='Bellevue') as BellevueResidents
--the subquery must be given a table name
--as alias

Select RequestMonth, ServiceName, Count(ServiceName) as [Count] 
From
(Select Month(GrantRequestDate) RequestMonth, 
GrantTypename ServiceName 
From GrantRequest gr
inner Join GrantType gt
on gr.GrantTypeKey=gt.GrantTypeKey) as GrantCount
Group by RequestMonth, ServiceName

--Common table Expressions are the same as 
--Table Expressions but structured differently
--they define the table query first 

go
with BellevueResidents as
(
 Select p.PersonKey, 
PersonLastName [Last],
PersonFirstName [First],
PersonAddressCity City
From Person p
inner join PersonAddress pa
on p.PersonKey =pa.PersonKey
Where PersonAddressCity='Bellevue'
)
Select [Last], [first], city From BellevueResidents

Go
With ServiceCount as
(
   Select Month(GrantRequestDate) RequestMonth, 
GrantTypename ServiceName 
From GrantRequest gr
inner Join GrantType gt
on gr.GrantTypeKey=gt.GrantTypeKey
)
Select RequestMonth, ServiceName, Count(ServiceName) [Count] from ServiceCount
Group by RequestMonth, ServiceName

Go
--this defines a variable
--and sets the value
--It is then used in the common table expression
Declare @GrantTypeKey int
Set @GrantTypeKey = 5;--one of the only place ; is required

With ServiceCount as
(
   Select Month(GrantRequestDate) RequestMonth, 
GrantTypename ServiceName 
From GrantRequest gr
inner Join GrantType gt
on gr.GrantTypeKey=gt.GrantTypeKey
Where gt.GrantTypeKey=@GrantTypeKey
)
Select RequestMonth, ServiceName, Count(ServiceName) [Count] from ServiceCount
Group by RequestMonth, ServiceName


--views are stored queries. 
--They can be used to provide
--a set of users views of the database
--that corrispond to how they use the database
Go
Alter view vw_HumanResources
AS
Select PersonLastName [LastName],
PersonFirstName [FirstName],
PersonEmail Email,
PersonAddressStreet [Address],
PersonAddressCity City,
PersonAddressZip ZipCode,
EmployeeHireDate HireDate
From Person p
Inner Join PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join Employee e
on e.PersonKey = p.PersonKey
go
Select [lastName], [FirstName], City, HireDate From vw_HumanResources
--won't work because you can't see underlying fields
Select PersonLastName from vw_HumanResources

go
-- a schema, in this context,
--is a collection of related objects
Create schema HumanResources
Go
--this view belongs to the humanResources schema
Create view HumanResources.vw_HR
AS
Select PersonLastName [LastName],
PersonFirstName [FirstName],
PersonEmail Email,
PersonAddressStreet [Address],
PersonAddressCity City,
PersonAddressZip ZipCode,
EmployeeHireDate HireDate
From Person p
Inner Join PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join Employee e
on e.PersonKey = p.PersonKey;


select * from HumanResources.vw_HR;

Select * From [SccITI\Kpowell].vw_hr
Go
--this is a table function
--it effectively
Create function fx_Employee
(@GrantTypeKey int)
returns Table
As
Return
Select GrantRequestKey, GrantRequestDate,PersonKey,
GrantTypeKey, GrantRequestExplanation, GrantRequestAmount
From GrantRequest
Where GrantTypeKey = @GrantTypeKey
Go

--table functions are called in the from
Select * from dbo.fx_Employee(3)