--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];
Thursday, January 28, 2016
Table Expressions Part 1
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment