x

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
more ▼

asked May 25, 2011 at 12:13 AM in Default

Ian Roke gravatar image

Ian Roke
1.7k 30 33 34

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)...!
May 25, 2011 at 12:54 AM Fatherjack ♦♦

+1 @Fatherjack

@Ian - you're no longer a "newbie" here at ASK, please help us to help you, thanks :)
May 25, 2011 at 01:02 AM WilliamD
Sorry I had to rush I was expected in a meeting. Going to update the question now @Fatherjack @WilliamD
May 25, 2011 at 01:27 AM Ian Roke

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?
May 25, 2011 at 01:51 AM WilliamD
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?
May 25, 2011 at 02:00 AM Ian Roke
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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%
more ▼

answered May 26, 2011 at 08:13 PM

Scot Hauder gravatar image

Scot Hauder
6k 13 15 18

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1948
x292
x21

asked: May 25, 2011 at 12:13 AM

Seen: 1159 times

Last Updated: May 25, 2011 at 04:47 AM