question

Ian Roke avatar image
Ian Roke asked

Survey completion rate by area

Last year I posted [this question][1] 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. [1]: http://ask.sqlservercentral.com/questions/6392/how-do-i-return-a-line-showing-a-count-of-completed-surveys-of-each-direct-user-under-a-particular-manager-recursively-in-a-survey
sql-server-2005tsqlrecursion
9 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
As always Ian - can we have some sample data + table structures please? The other question had a few revisions of these and I wouldnt want to pick the wrong one(s)...!
4 Likes 4 ·
WilliamD avatar image WilliamD commented ·
+1 @Fatherjack @Ian - you're no longer a "newbie" here at ASK, please help us to help you, thanks :)
2 Likes 2 ·
Ian Roke avatar image Ian Roke commented ·
Sorry I had to rush I was expected in a meeting. Going to update the question now @Fatherjack @WilliamD
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
I don't see how the expected output matches to your sample data. The way I understand it, you count the number of entries where QuestionId = 24 for each subordinate of a manager (JobBand='MNGR') and include the manager in the calculation too. If so, then your expected output for the test data supplied would be: UserName | JobBand | Completed | Total | Completed/Total ======================================================== Joe | MNGR | 3 | 7 | 43% Paul | MNGR | 2 | 3 | 60% Am I right, or have I misunderstood?
0 Likes 0 ·
Ian Roke avatar image Ian Roke commented ·
No the manager isn't included in their results but in the next tier in the structure up however I am not interested in anything above MNGR because that is the manager of the MNGR tier. Does that make sense?
0 Likes 0 ·
Show more comments

1 Answer

·
Scot Hauder avatar image
Scot Hauder answered
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%
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.