Table Expressions Part 1

--Derived tables
Use CommunityAssist

Select Personkey, [Last], [First], City from (Select p.Personkey, PersonLastName [Last],
   PersonFirstName [First], City
   From Person p
   inner Join PersonAddress pa
   on p.PersonKey=pa.personKey
   where city = 'Bellevue') as BellevueResidents

   Select RequestMonth, ServiceName, Count(ServiceName) as [Count] From
   (Select month(GrantDate) RequestMonth, ServiceName
   From ServiceGrant sg
   inner join CommunityService cs
   on sg.ServiceKey=cs.ServiceKey) as ServiceCount
   Group by RequestMonth, ServiceName

   --Common Table Expressions

   with BellevueResidents as
  (
  Select p.Personkey, PersonLastName [Last],
     PersonFirstName [First], City
     From Person p
     inner Join PersonAddress pa
     on p.PersonKey=pa.personKey
     where city = 'Bellevue'
  )
  Select [Last], [First], City From BellevueResidents

  with ServiceCount as
  (
    (Select month(GrantDate) RequestMonth, ServiceName
   From ServiceGrant sg
   inner join CommunityService cs
   on sg.ServiceKey=cs.ServiceKey)
  )
  Select RequestMonth, ServiceName, Count(ServiceName) as [Count] From ServiceCount
  Group by RequestMonth, ServiceName

  Declare @EmployeeKey int;
  Set @EmployeeKey=4;

  with EmployeeServiceGrants as
  (
  Select Month(GrantDate) as [Month], GrantKey
  From ServiceGrant
  Where EmployeeKey=@EmployeeKey
  )
  Select [Month], Count(GrantKey) as Total
  From EmployeeServiceGrants
  Group by [Month];