How to modify this query to show all the Divisions in the last three months?

Hello everybody,

I am developing a training management web-based system which will show the management the training record for each division. My database design is like following:

Employee Table: Username, Name, Job, DivisionID

Division Table: DivisionID, DivisionName

Quiz Table: QuizID, Title, Description

UserQuiz Table: UserQuizID, Score, DateTimeComplete, QuizID, Username

NOTE: The first attribute in each table is the primary key.

The SQL Query that I am using for this task is:

 SELECT COUNT(DISTINCT dbo.UserQuiz.QuizID) AS [Total Number of Quizzes],  
        DATENAME(Month, dbo.UserQuiz.DateTimeComplete) AS Month 
       dbo.Quiz ON dbo.UserQuiz.QuizID = dbo.Quiz.QuizID
       INNER JOIN  dbo.employee ON dbo.UserQuiz.Username = dbo.employee.Username
       RIGHT OUTER JOIN  dbo.Divisions ON dbo.employee.DivisionCode = dbo.Divisions.SapCode
 GROUP BY dbo.Divisions.DivisionName, 
    DATENAME(Month, dbo.UserQuiz.DateTimeComplete)

This query will show me the total number of taken quizzes by each division based on month. What I want now is showing these results for the last three months. Also, I want to show all the divisions even if there is a division which does not take any quiz. This means I want to show the division with zero number of taken quizzes.

more ▼

asked Dec 10, 2011 at 10:40 PM in Default

avatar image

Mohammed Al-Ali
11 2 2 2

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

1 answer: sort voted first

If you need to filter a result set, generally the best approach is to add a WHERE clause. But, in this case, your filter needs to be in the JOIN criteria to the test table or you'll eliminate the divisions that haven't taken a test.

If you need to work off of datetime values and do something like "last three months" why not do a little date math:

 DateTimeComplete > DATEADD(month,-3,GETDATE())

That's the simplest way to do it. There are more sophisticated things you can do, but that'll get you started. Plus, this way avoids performing functions on your column so the query is still going to work with indexes well.

more ▼

answered Dec 11, 2011 at 03:53 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

I am already trying to use this but I am failing. Could you please help me with it? This thing is a new to me

Dec 11, 2011 at 04:10 AM Mohammed Al-Ali
(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Dec 10, 2011 at 10:40 PM

Seen: 1292 times

Last Updated: Dec 11, 2011 at 05:38 AM

Copyright 2018 Redgate Software. Privacy Policy