x
login about faq Site discussion (meta-askssc)

How to show the total number of employees in this database design?

I have the following database design:

Employee Table: EmployeeID, Name, OrgCode
Department Table: OrgCode, DepartName
CompleteSurvey Table: ID, RespondantID, QuestionsAnswersID
Questions Table: QuestionID, Question
Answers Table: AnswerID, Answer
QuestionsAnswers Table: ID, QuestionID, AnswerID

I wrote a query that shows each question with all possible answers and the total number of participants in each question even if there is one of the possible answers without any participant. This results will be shown in each department.

What I have to do now is to modify this query to show the total number of employees in each department besides the total number of participants that I already got, then show the participation percentage which is the total number of participants/ total number of employees.

So how to do that?

The query:

SELECT     
   TOP (100) PERCENT 
   COUNT(DISTINCT CompleteSurvey.RespondantID) AS [Total Number of Participants], 
   dbo.Answers.Answer, dbo.Questions.Question, 
   dbo.Departments.DepartmentName
FROM
   dbo.Questions 
INNER JOIN
   dbo.QuestionsAnswers 
   ON dbo.Questions.QuestionID = dbo.QuestionsAnswers.QuestionID 
INNER JOIN
   dbo.Answers ON dbo.QuestionsAnswers.AnswerID = dbo.Answers.AnswerID 
CROSS JOIN
   dbo.Departments 
LEFT OUTER JOIN
   (SELECT     
        dbo.Employees.OrgCode, CompleteSurvey_1.QuestionsAnswersID, 
        CompleteSurvey_1.RespondantID
    FROM          
        dbo.CompleteSurvey AS CompleteSurvey_1 
    INNER JOIN
        dbo.Employees 
        ON dbo.Employees.EmployeeID = CompleteSurvey_1.RespondantID) AS CompleteSurvey 
        ON dbo.QuestionsAnswers.ID = CompleteSurvey.QuestionsAnswersID 
               AND dbo.Departments.OrgCode = CompleteSurvey.OrgCode
GROUP BY 
    dbo.Answers.Answer, dbo.Questions.Question, dbo.Departments.DepartmentName
ORDER BY 
    dbo.Questions.Question, dbo.Answers.Answer, dbo.Departments.DepartmentName
more ▼

asked May 27 '12 at 04:15 AM in Default

essence388 gravatar image

essence388
21 5 11 13

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

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

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



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x394
x32

asked: May 27 '12 at 04:15 AM

Seen: 485 times

Last Updated: May 28 '12 at 08:38 AM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.