Aggregate survey results recursively by manager
- by Ian Roke
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
