Table Expressions (Queries in the FROM), VIews

Use MetroAlt

Select Year(BusScheduleAssignmentDate) [Year], Sum(riders) Annual,
Avg(riders) Average,
(Select sum(riders) From Ridership) Total,
(Cast(Sum(riders)as decimal(10,2))/(Select sum(riders) From Ridership))*100 [Percent]
From ridership
Inner Join BusScheduleAssignment
on ridership.BusScheduleAssigmentKey=BusScheduleAssignment.BusScheduleAssignmentKey
Group by Year(BusScheduleAssignmentDate)
Order by Year

--table expressions
Use Community_Assist

Select LastName, FirstName, Email, City
From (Select PersonLastName [LastName],
PersonFirstName FirstName,
PersonEmail Email,
PersonAddressCity City
From Person
Inner join PersonAddress
on Person.PersonKey=PersonAddress.PersonKey
Where PersonAddressCity='Bellevue') as BellevueResidents

Select RequestMonth, GrantTypeName, count(GrantTypekey) as [Count] From
   (Select month(GrantRequestDate) RequestMonth, sg.GrantTypeKey, GrantTypeName
   From GrantRequest sg
   inner join GrantType cs
   on sg.GrantTypeKey=cs.GrantTypeKey) as ServiceCount
   Group by RequestMonth, GrantTypeName
   Order by RequestMonth
   
--Common table expressions
with BellevueResidents as
(
   Select PersonLastName LastName,
   PersonFirstName FirstName,
   PersonEmail Email,
   PersonAddressCity City
   From Person
   Inner Join PersonAddress
   On person.PersonKey=PersonAddress.PersonKey
   where PersonAddressCity='Bellevue'
)
Select LastName, FirstName, Email, City
From BellevueResidents;

with ServiceCount as
(
 Select month(GrantRequestDate) RequestMonth, sg.GrantTypeKey, GrantTypeName
   From GrantRequest sg
   inner join GrantType cs
   on sg.GrantTypeKey=cs.GrantTypeKey
)
Select RequestMonth, GrantTypeName, Count(GrantTypeKey) [Count]
From ServiceCount
Group by RequestMonth, GrantTypeName
Order by RequestMonth;

Declare @City Nvarchar(255)='Kent';
--Set @City='Kent';

with BellevueResidents as
(
   Select PersonLastName LastName,
   PersonFirstName FirstName,
   PersonEmail Email,
   PersonAddressCity City
   From Person
   Inner Join PersonAddress
   On person.PersonKey=PersonAddress.PersonKey
   where PersonAddressCity=@City
)
Select LastName, FirstName, Email, City
From BellevueResidents;
go

create function fx_CityFunction
(@City nvarchar(255))
returns table
As
return
   Select PersonLastname , 
   PersonFirstName ,
   PersonEmail ,
   PersonAddressCity
   From Person
   inner join PersonAddress
   on person.PersonKey = PersonAddress.PersonKey
   Where PersonAddressCity=@City


Select * from dbo.fx_CityFunction('Shoreline')

go
Create view Vw_HumanResouces
As
Select PersonFirstName [First name],
PersonLastname [Last Name],
PersonEmail [Email],
EmployeeHireDate [Hire Date],
EmployeeAnnualSalary [Annual Salary],
PositionName [Position]
From Person
Inner Join Employee
On Person.PersonKey=Employee.PersonKey
Inner Join EmployeePosition
On Employee.EmployeeKey = EmployeePosition.Employeekey
inner join Position
on Position.PositionKey=EmployeePosition.PositionKey

go

Select * From Vw_HumanResouces
Where Position='Associate'

Select [Last Name] from Vw_HumanResouces

Go
Alter view Vw_HumanResouces with Schemabinding
As
Select PersonFirstName [First name],
PersonLastname [Last Name],
PersonEmail [Email],
EmployeeHireDate [Hire Date],
EmployeeAnnualSalary [Annual Salary],
PositionName [Position]
From dbo.Person
Inner Join dbo.Employee
On dbo.Person.PersonKey=dbo.Employee.PersonKey
Inner Join dbo.EmployeePosition
On dbo.Employee.EmployeeKey = dbo.EmployeePosition.Employeekey
inner join dbo.Position
on dbo.Position.PositionKey=dbo.EmployeePosition.PositionKey

go
Begin tran
Alter table Person
Drop column PersonEmail
Rollback tran