How to modify this sql query?

Question

4.00/5 (1 vote)

See more:

I developed a quiz engine similar to this one in the ASP.NET website, but I just added a new table to the design of the database. The original design of the database consists of three tables as following:

Quiz Table: QuizID, Title, Description
Question Table: QuestionID, Question, Answer1, Answer2, Answer3, Answer4, CorrectAnswer, AnswerExplanation, QuestionOrder, QuizID
UserQuiz Table: UserQuizID, QuizID, DateTimeComplete, Score, Username

The first attribute in each table is the primary key of that table

What I did is just adding a few tables which are:
User Table: Username, Name, JobTitle, DivisionCode (Username is the primary key of this table)
Divisions Table: DivisionCode, DivisionName

I am developing now a dashboard that should contain some charts and statistics in table. For that, with a help from this great community I came up with some powerful queries that was working on the original database design. Now, I need to modify these queries. I am struggled a lot with this query:

SQL

SELECT
 (SELECT COUNT(DISTINCT Q.UserName)
 FROM dbo.UserQuiz Q
 WHERE DateTimeComplete >= DATEADD(dd, -7, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)))
 as ParticipantsLast7Days,
 (SELECT COUNT(Q.QuizID)
 FROM dbo.UserQuiz Q
 WHERE Q.DateTimeComplete >= DATEADD(dd, -7, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)))
 as QuizzesLast7Days,
 (SELECT COUNT(DISTINCT Q.UserName)
 FROM dbo.UserQuiz Q
 WHERE DateTimeComplete >= DATEADD(dd, -30, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)))
 as ParticipantsLast30Days,
 (SELECT COUNT(Q.QuizID)
 FROM dbo.UserQuiz Q
 WHERE Q.DateTimeComplete >= DATEADD(dd, -30, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)))
 as QuizzesLast30Days

As you see, it will show me the total number of taken quizzes last week and last month, and the total number of participants last week and last month.

**Now, with the current (new) design of the database, it should show me the total number of taken quizzes and the total number of participants in all quizzes in each division on last week and last month. Plus, it will be great if it shows the total number of employees in each division with displaying the number of employees who did not participate at all in any quiz**

The problem is I don't know how to modify it to get these last things.

Posted 6-Dec-11 1:51am

matrix388

Updated 6-Dec-11 1:55am

Mehdi Gholam

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

FastEvo8 · Answer 1 · 2011-12-06T04:58:00

try this:

SQL

;WITH cteTemp 
AS
(
SELECT Username
FROM dbo.Quiz Q
where Q.DateTimeComplete >= DATEADD(dd, -30, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
group by q.Username
)
SELECT COUNT(u.Username)
FROM UserTable U	 
left outer join cteTemp c
ON c.Username=u.Username
where c.UsernameIS NULL