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