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?
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
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.
May 25 '11 at 12:13 AM