x

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, 2012 at 04:15 AM in Default

essence388 gravatar image

essence388
21 11 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.

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:

x581
x45

asked: May 27, 2012 at 04:15 AM

Seen: 3144 times

Last Updated: May 28, 2012 at 08:38 AM