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

avatar image

essence388
21 11 11 14

(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

SQL Server Central

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

Topics:

x748
x51

asked: May 27, 2012 at 04:15 AM

Seen: 4999 times

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

Copyright 2016 Redgate Software. Privacy Policy