I think you could also just use a CTE such as:

;WITH CTE AS
(
SELECT 
 us.Name ,us.TeamID
 ,SUM(wl.HoursWorked) HoursWorked,
 ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY Name) RN
FROM Users us
INNER JOIN WorkLog...