Survey completion rate by area

Question

Last year I posted this question and I got a breakdown of completed surveys by manager.

This year I would like to expand this slightly by introducing JobBand which is in the StaffLookup table. I would like to be able to say WHERE JobBand = 'MNGR' and it would show each of the staff who have a job band of MNGR and a percentage of staff underneath them who have completed the survey against the total number of staff.

Please can I have some pointers on how I would do this?

Edit:

StaffLookup table:

UserSrn | UserName | ManagerSrn | JobBand
=========================================
ABC1    | Jerome   | NULL       | HEAD
ABC2    | Joe      | ABC1       | MNGR
ABC3    | Paul     | ABC2       | MNGR
ABC4    | Jack     | ABC3       | A
ABC5    | Daniel   | ABC3       | A
ABC6    | David    | ABC2       | B
ABC7    | Ian      | ABC6       | C
ABC8    | Helen    | ABC6       | C

SurveyResponses table:

UserSrn | QuestionId | ResponseScore
====================================
ABC2    | 1          | 5
ABC2    | 3          | 4
ABC4    | 24         | 0
ABC3    | 24         | 0

What I would like to see:

UserName | JobBand | Completed | Total | Completed/Total
========================================================
Joe      | MNGR    | 2         | 4     | 50%
Paul     | MNGR    | 2         | 2     | 100%

Completed is a 1 when there is a QuestionID of 24. Total is just 1 for each user. I hope this makes sense. Let me know if more detail is required.

Answer 1

Hi Ian

;WITH OrgChart AS (
    SELECT sl.UserSrn, sl.UserName [User Name], sl.JobBand [Job Band], [lvl] = 0
    FROM StaffLookup sl
    WHERE JobBand = 'MNGR'
    UNION ALL
    SELECT e.UserSrn, [User Name], [Job Band], [lvl] + 1
    FROM OrgChart o 
    JOIN StaffLookup e ON (e.ManagerSrn = o.UserSrn) 
)
,SurveyTotal AS (
    SELECT [User Name], [Job Band], CASE WHEN QuestionID = 24 THEN 1.0 ELSE 0 END [Completed], 
           [Total] = 1.0, [lvl]
    FROM OrgChart o 
    LEFT JOIN SurveyResponse sr ON (sr.UserSrn = o.UserSrn AND sr.QuestionID = 24)
)
SELECT [User Name], [Job Band], SUM(Completed)[Completed], SUM(Total)[Total],
       CONVERT(varchar(6),CAST(SUM(Completed)/SUM(Total)*100 AS int)) + '%' [Completed/Total]
FROM SurveyTotal 
WHERE [lvl] <> 0
GROUP BY [User Name], [Job Band]

User Name Job Band Completed Total Completed/Total
--------- -------- --------- ----- ---------------
Joe       MNGR     2.0       6.0   33%
Paul      MNGR     1.0       2.0   50%

Survey completion rate by area

Edit:

Follow this question

Related Questions