Aggregate survey results recursively by manager

I have a StaffLookup table which looks like this.

UserSrn | UserName | ManagerSrn
===============================
ABC1    | Jerome   | NULL
ABC2    | Joe      | ABC1
ABC3    | Paul     | ABC2
ABC4    | Jack     | ABC3
ABC5    | Daniel   | ABC3
ABC6    | David    | ABC2
ABC7    | Ian      | ABC6
ABC8    | Helen    | ABC6

The staff structure looks like this.

|- Jerome
 |
 |- Joe
 ||
 ||- Paul
 |||
 |||- Jack
 |||
 |||- Daniel
 ||
 ||- David
 |||
 |||- Ian
 |||
 |||- Helen

I have a list of SurveyResponses that looks like this.

UserSrn | QuestionId | ResponseScore
====================================
ABC2    | 1          | 5
ABC2    | 3          | 4
ABC4    | 16         | 3
...

What I am trying to do sounds pretty simple but I am struggling to find a neat, quick way of doing it. I want to create a sproc that takes an Srn and returns back all the staff under that Srn in the structure.

If there is a score for QuestionId of 16 then that indicates a completed survey. I would like to return a line for the Srn entered (The top manager) with a count of completed surveys for the direct reports under that manager. Under that I would like each manager under the original manager with a count of completed surveys for each of their direct reports and so on.

I would like to see the data as such below when I set the top manager to be Joe (ABC2).

UserName | Completed | Total
============================
Joe      | 2         | 2
Paul     | 1         | 2
David    | 0         | 2
TOTAL    | 3         | 6

I believe this works, based on the information provided. It shouldn't be too difficult to turn this into an SP with @mgrSrn as the input parameter.

declare @users table
(UserSrn char(4)
,UserName varchar(6)
,ManagerSrn char(4)
)

INSERT @users
      SELECT 'ABC1','Jerome',NULL
UNION SELECT 'ABC2','Joe','ABC1'
UNION SELECT 'ABC3','Paul','ABC2'
UNION SELECT 'ABC4','Jack','ABC3'
UNION SELECT 'ABC5','Daniel','ABC3'
UNION SELECT 'ABC6','David','ABC2'
UNION SELECT 'ABC7','Ian','ABC6'
UNION SELECT 'ABC8','Helen','ABC6'

declare @results table
(UserSrn char(4)
,QuestionId tinyint
,ResponseScore tinyint
)

INSERT @results
      SELECT 'ABC2',1,1
UNION SELECT 'ABC4',16,1

declare @mgrSrn char(4)
set @mgrSrn = 'ABC2' -- Joe


;WITH completedCTE
AS
(
    SELECT c.*
           ,CASE WHEN r.UserSrn IS NOT NULL
                 THEN 1
                 ELSE 0
            END     AS completeCount
           ,1       AS totalCount
    FROM      @users as c
    LEFT JOIN @results AS r
    ON        r.UserSrn    = c.UserSrn
    AND       r.QuestionId = 16
)
,recCTE
AS
(
    SELECT  UserSrn
            ,UserName
            ,CAST(NULL AS CHAR(4)) AS ManagerSrn
            ,1 as level
            ,completeCount 
            ,totalCount
    FROM completedCTE
    WHERE UserSrn = @mgrSrn

    UNION ALL

    SELECT t.UserSrn
           ,t.UserName
           ,t.ManagerSrn
           ,c.level + 1 AS level
           ,t.completeCount AS completeCount
           ,t.totalCount AS totalCount
    FROM completedCTE AS t
    JOIN recCTE AS c
    ON   c.UserSrn = t.ManagerSrn


)
,resultCTE
AS
(
    SELECT r.ManagerSrn
           ,t.UserName
           ,r.level
           ,SUM(completeCount) completeCount
           ,SUM(totalCount)    totalCount
    FROM recCTE AS r
    JOIN @users     AS t
    ON  t.UserSrn = r.ManagerSrn
    WHERE r.ManagerSrn IS NOT NULL
    GROUP BY r.ManagerSrn
             ,t.UserName 
             ,r.level

)
SELECT UserName
       ,completeCount
       ,totalCount
FROM resultCTE  
ORDER BY level
         ,UserName   
OPTION (MAXRECURSION 0) 

try this:

DECLARE @Staff table (UserSrn char(4), UserName varchar(10), ManagerSrn char(4))
INSERT @Staff VALUES ('ABC1','Jerome', NULL )
INSERT @Staff VALUES ('ABC2','Joe'   ,'ABC1')
INSERT @Staff VALUES ('ABC3','Paul'  ,'ABC2')
INSERT @Staff VALUES ('ABC4','Jack'  ,'ABC3')
INSERT @Staff VALUES ('ABC5','Daniel','ABC3')
INSERT @Staff VALUES ('ABC6','David' ,'ABC2')
INSERT @Staff VALUES ('ABC7','Ian'   ,'ABC6')
INSERT @Staff VALUES ('ABC8','Helen' ,'ABC6')

DECLARE @SurveyResponses table (UserSrn char(4), QuestionId int, ResponseScore int)
INSERT @SurveyResponses VALUES ('ABC2',1 ,5)
INSERT @SurveyResponses VALUES ('ABC2',3 ,4)
INSERT @SurveyResponses VALUES ('ABC6',16,3)

DECLARE @RootUserSrn  char(4)
SET @RootUserSrn='ABC2'

--get tree of given user
;WITH StaffTree AS
(
    SELECT 
        UserSrn, UserName, ManagerSrn, UserSrn AS ManagerUserSrn, UserName AS ManagerUserName, 1 AS LevelOf
        FROM @Staff
        WHERE UserSrn=@RootUserSrn
    UNION ALL
        SELECT 
            s.UserSrn, s.UserName, s.ManagerSrn, t.UserSrn, t.UserName, t.LevelOf+1
        FROM StaffTree         t
            INNER JOIN @Staff  s ON t.UserSrn=s.ManagerSrn
        WHERE s.ManagerSrn=@RootUserSrn

)
SELECT 
    s.UserName,COUNT(r.QuestionId) AS Completed,'???' as total

    FROM StaffTree                        s
        LEFT OUTER JOIN @SurveyResponses  r ON s.UserSrn=r.UserSrn
    GROUP BY s.UserName,s.LevelOf
    ORDER BY s.LevelOf

OUTPUT:

UserName   Completed   total
---------- ----------- -----
Joe        2           ???
David      1           ???
Paul       0           ???

EDIT after OP's comments:

DECLARE @Staff table (UserSrn char(4), UserName varchar(10), ManagerSrn char(4))
INSERT @Staff VALUES ('ABC1','Jerome', NULL )
INSERT @Staff VALUES ('ABC2','Joe'   ,'ABC1')
INSERT @Staff VALUES ('ABC3','Paul'  ,'ABC2')
INSERT @Staff VALUES ('ABC4','Jack'  ,'ABC3')
INSERT @Staff VALUES ('ABC5','Daniel','ABC3')
INSERT @Staff VALUES ('ABC6','David' ,'ABC2')
INSERT @Staff VALUES ('ABC7','Ian'   ,'ABC6')
INSERT @Staff VALUES ('ABC8','Helen' ,'ABC6')

DECLARE @SurveyResponses table (UserSrn char(4), QuestionId int, ResponseScore int)
INSERT @SurveyResponses VALUES ('ABC2',1 ,5)
INSERT @SurveyResponses VALUES ('ABC2',3 ,4)
INSERT @SurveyResponses VALUES ('ABC6',16,3)

DECLARE @RootUserSrn  char(4)
SET @RootUserSrn='ABC2'

--get tree of given user
;WITH StaffTree AS
(
    SELECT 
        UserSrn, UserName, ManagerSrn, UserSrn AS ManagerUserSrn, UserName AS ManagerUserName, 1 AS LevelOf
        FROM @Staff
        WHERE UserSrn=@RootUserSrn
    UNION ALL
        SELECT 
            s.UserSrn, s.UserName, s.ManagerSrn, t.UserSrn, t.UserName, t.LevelOf+1
        FROM StaffTree         t
            INNER JOIN @Staff  s ON t.UserSrn=s.ManagerSrn
        WHERE s.ManagerSrn=@RootUserSrn

)
, MINLevel AS (
    SELECT MIN(LevelOf) AS MinLevelOf FROM StaffTree
)
, TotalLevel AS (
    SELECT
        SUM(CASE WHEN s.LevelOf !=m.MinLevelOf THEN 1 ELSE 0 END) AS TotalOf
        FROM StaffTree            s
            CROSS JOIN MINLevel   m
)
,Results AS (
    SELECT 
        s.UserName,SUM(CASE WHEN r.QuestionId=16 THEN 1 ELSE 0 END) AS Completed,t.TotalOf as total,s.LevelOf

        FROM StaffTree                        s
            LEFT OUTER JOIN @SurveyResponses  r ON s.UserSrn=r.UserSrn
            CROSS JOIN TotalLevel             t
        GROUP BY s.UserName,s.LevelOf,t.TotalOf
)
SELECT
    UserName,Completed,total, 1,LevelOf
    FROM Results
UNION ALL
    SELECT
        'TOTAL',SUM(Completed),SUM(total),2,0
        FROM Results
ORDER BY 4,5

OUTPUT:

UserName   Completed   total                   LevelOf
---------- ----------- ----------- ----------- -----------
Joe        0           2           1           1
David      1           2           1           2
Paul       0           2           1           2
TOTAL      1           6           2           0

(4 row(s) affected)

I still can not see how the given data, results in Joe having completed=2 and Paul having completed 1. I changed the given data from ('ABC4',16,3) to ('ABC6',16,3) so someone in the result set would have one completed.

Edit: I used SQL Server 2008 to generate the INSERT statements...

I can generate your hierarchy, but not the results. The sample input and output data don't tie up, sorry.

You'll need LevelNum to tie in the results most likely to the hierarchy

DECLARE @staff TABLE (UserSrn char(4), UserName varchar(10), ManagerSrn char(4))
INSERT @staff (UserSrn, UserName, ManagerSrn)
VALUES 
('ABC1'    , 'Jerome'   , NULL),('ABC2'    , 'Joe'      , 'ABC1'),
('ABC3'    , 'Paul'     , 'ABC2'),('ABC4'    , 'Jack'     , 'ABC3'),
('ABC5'    , 'Daniel'   , 'ABC3'),('ABC6'    , 'David'    , 'ABC2'),
('ABC7'    , 'Ian'      , 'ABC6'),('ABC8'    , 'Helen'    , 'ABC6')

DECLARE @results TABLE (UserSrn char(4), QuestionId varchar(10), ResponseScore char(4))
INSERT @results (UserSrn, QuestionId, ResponseScore)
VALUES ('ABC2'    , 2   , 5),('ABC2'    , 3      , 4),('ABC4'    , 16     , 3)

;WITH cHierarchy AS
(
    SELECT
       s.UserSrn, S.UserName, S.ManagerSrn, CAST('|' AS varchar(50)) AS LevelStr, 0 AS LevelNum
    FROM
       @staff S
    WHERE
       S.ManagerSrn IS NULL
    UNION ALL
    SELECT
       s.UserSrn, S.UserName, S.ManagerSrn, CAST(Level + '|' AS varchar(50)), LevelNum + 1
    FROM
       cHierarchy C JOIN @staff S ON C.UserSrn = S.ManagerSrn
)
SELECT
    *
FROM
    cHierarchy C