question

matrix388 avatar image
matrix388 asked

How to fix this query to show the results in the last three months?

Hello everybody, I found an unanswered question here relate to my question which is showing results in the last three months. It is really interesting area in SQL. I tried to use DATENAME, DATEPART and DATEDIFF but I failed with all of them. The question is: I have the following database design: 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 trying to use for showing the results in the last three months is: SELECT COUNT(DISTINCT dbo.UserQuiz.QuizID) AS [Total Number of Quizzes], dbo.Divisions.DivisionName, DATENAME(Month, dbo.UserQuiz.DateTimeComplete) AS Month FROM dbo.UserQuiz INNER JOIN 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) Also, I tried many queries and each time got something different from what I want or got an error related to the syntax **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.**
sql-server-2008sql
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
Please provide us some sample data and desired output to get a certain answer.
1 Like 1 ·
BarneyL avatar image BarneyL commented ·
By "The last three months" do you mean the period going back exactly three months from today (e.g. 12th September - 12 October) or the last three completed months (1st September to 30th November). Also how long will you store the data for? At present your query would group results from November 2009, 2010 and 2011 all together as there is no grouping on year.
1 Like 1 ·
Usman Butt avatar image
Usman Butt answered
Since you did not provide the sample data and desired output, this is something assumed to get you started. Tweak it according to your need. DECLARE @LastDayOfPrevMonth DATETIME, @FirstDayOfThreeMonthsBefore DATETIME SET @FirstDayOfThreeMonthsBefore = DATEADD(MONTH, -2, DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)) SET @LastDayOfPrevMonth = GETDATE() ;WITH MonthCTE AS ( SELECT DATENAME(MONTH, DATEADD(MONTH, -2, CURRENT_TIMESTAMP)) + '-' + DATENAME(YEAR, DATEADD(MONTH, -2, CURRENT_TIMESTAMP)) AS MonthYear UNION ALL SELECT DATENAME(MONTH, DATEADD(MONTH, -1, CURRENT_TIMESTAMP)) + '-' + DATENAME(YEAR, DATEADD(MONTH, -1, CURRENT_TIMESTAMP)) UNION ALL SELECT DATENAME(MONTH, DATEADD(MONTH, 0, CURRENT_TIMESTAMP)) + '-' + DATENAME(YEAR, DATEADD(MONTH, 0, CURRENT_TIMESTAMP)) ) SELECT Divisions.DivisionName , ISNULL([Total Number of Quizzes],0) [Total Number of Quizzes] , MonthYear [Month] FROM #Divisions Divisions OUTER APPLY ( SELECT [Total Number of Quizzes], MonthCTE.MonthYear FROM (SELECT COUNT(DISTINCT UserQuiz.QuizID) AS [Total Number of Quizzes], DATENAME(MONTH, UserQuiz.DateTimeComplete) + '-' + DATENAME(YEAR, UserQuiz.DateTimeComplete) MonthYear FROM #UserQuiz UserQuiz INNER JOIN #Quiz Quiz ON UserQuiz.QuizID = Quiz.QuizID INNER JOIN #employee employee ON UserQuiz.Username = employee.Username WHERE employee.DivisionCode = Divisions.SapCode AND UserQuiz.DateTimeComplete BETWEEN @FirstDayOfThreeMonthsBefore AND @LastDayOfPrevMonth GROUP BY DATENAME(MONTH, UserQuiz.DateTimeComplete), DATENAME(YEAR, UserQuiz.DateTimeComplete) )Quiz RIGHT JOIN MonthCTE ON Quiz.MonthYear = MonthCTE.MonthYear ) QuizMonthOutput
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

matrix388 avatar image matrix388 commented ·
Thanks a lot for your help. I really appreciate it. Your work is amazing. Sorry because I did not provide you with the sample data because I was away. I will see your query now based on the requirements that I have and if I need any further explanation, I will ask you. BTW, I have a small question. Based on what I saw from your query, the result will be shown based on Month with mentioning the Year. Did you put the Year because in case if it is not existed, the results of November 2010 will be added to November 2011?
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
Yes, while grouping the data by month, you should take year into consideration as well.
0 Likes 0 ·
matrix388 avatar image matrix388 commented ·
Thanks. I got the concept.
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
Here is much simpler query to accomplish this - it uses table variable names. The full example follows after the initial query: SELECT D.DivisionID, D.Name AS DivisionName, YEAR(UQ.DateTimeComplete) AS YearTaken, MONTH(UQ.DateTimeComplete) AS MonthTaken, COUNT(UQ.UserQuizID) AS QuizesTaken FROM @Division D LEFT JOIN @Employee E ON D.DivisionID = E.DivisionID LEFT JOIN @UserQuiz UQ ON E.UserName = UQ.Username AND UQ.DateTimeComplete BETWEEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 2, 0) AND GETDATE() GROUP BY D.DivisionID, D.Name, YEAR(UQ.DateTimeComplete), MONTH(UQ.DateTimeComplete) Depending on how you would like to treat the last three months, you can use alternate BETWEEN in th query: `BETWEEN DATEADD(month, -2, GETDATE()) AND GETDATE()` **== FULL SAMPLE ==** DECLARE @Employee TABLE ( UserName varchar(10) NOT NULL PRIMARY KEY CLUSTERED, Name varchar(30), Job varchar(30), DivisionID int ) DECLARE @Division TABLE( DivisionID int NOT NULL PRIMARY KEY CLUSTERED, Name varchar(30) ) DECLARE @Quiz TABLE( QuizID int NOT NULL PRIMARY KEY CLUSTERED, Title varchar(30), Description varchar(50) ) DECLARE @UserQuiz TABLE( UserQuizID int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, Score int, DateTimeComplete datetime, QuizID int, Username varchar(10) ) INSERT INTO @Division (DivisionID, Name) SELECT 1, 'Division 1' UNION ALL SELECT 2, 'Division 2' UNION ALL SELECT 3, 'Division 3' UNION ALL SELECT 4, 'Division 4' INSERT INTO @Employee(UserName, Name, Job, DivisionID) SELECT 'empl1', 'Employee 1', 'Job', 1 UNION ALL SELECT 'empl2', 'Employee 2', 'Job', 1 UNION ALL SELECT 'empl3', 'Employee 3', 'Job', 2 UNION ALL SELECT 'empl4', 'Employee 4', 'Job', 2 UNION ALL SELECT 'empl5', 'Employee 5', 'Job', 3 UNION ALL SELECT 'empl6', 'Employee 6', 'Job', 3 UNION ALL SELECT 'empl7', 'Employee 7', 'Job', 4 INSERT INTO @Quiz (QuizID, Title, Description) SELECT 1, 'Q1', 'Quiz 1' UNION ALL SELECT 2, 'Q2', 'Quiz 2' INSERT INTO @UserQuiz (Username, QuizID, Score, DateTimeComplete) SELECT 'empl1', 1, 100, '2011/12/10' UNION ALL SELECT 'empl1', 1, 100, '2011/11/25' UNION ALL SELECT 'empl2', 1, 100, '2011/10/12' UNION ALL SELECT 'empl2', 1, 100, '2011/07/13' UNION ALL SELECT 'empl3', 1, 100, '2011/10/10' UNION ALL SELECT 'empl4', 1, 100, '2011/12/08' UNION ALL SELECT 'empl4', 1, 100, '2011/11/09' UNION ALL SELECT 'empl4', 1, 100, '2011/11/11' UNION ALL SELECT 'empl5', 1, 100, '2011/12/10' UNION ALL SELECT 'empl5', 1, 100, '2011/01/10' UNION ALL SELECT 'empl6', 1, 100, '2011/12/10' UNION ALL SELECT 'empl6', 1, 100, '2011/01/10' UNION ALL SELECT 'empl6', 1, 100, '2010/10/10' SELECT D.DivisionID, D.Name AS DivisionName, YEAR(UQ.DateTimeComplete) AS YearTaken, MONTH(UQ.DateTimeComplete) AS MonthTaken, COUNT(UQ.UserQuizID) AS QuizesTaken FROM @Division D LEFT JOIN @Employee E ON D.DivisionID = E.DivisionID LEFT JOIN @UserQuiz UQ ON E.UserName = UQ.Username AND UQ.DateTimeComplete BETWEEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 2, 0) AND GETDATE() --BETWEEN DATEADD(month, -2, GETDATE()) AND GETDATE() GROUP BY D.DivisionID, D.Name, YEAR(UQ.DateTimeComplete), MONTH(UQ.DateTimeComplete)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mohammed Al-Ali avatar image
Mohammed Al-Ali answered
Hello everbody, I really like this great community which has many great resources about the SQL, in addition to, the experts in the SQL. Regarding this question. I am currently facing a situation similar to this one. And the query posted by Usman Butt helped me a lot, but I need to tweak it to show the results starting from day which means it should show the results for the Oct 2011, Nov 2011, Dec 2011. I tried a lot but I failed, ***so HOW TO DO THAT?***
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
Are you saying you just want to see the data for months only? You do not need Divisions in the output?
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
OR you are not able to format the Month field i.e. from September-2011 to Sep, 2011? Please provide the desired output sketch to get a certain answer.
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
OK, I get it. I have changed it in my original answer. Only the declaration part was needed to be changed.
0 Likes 0 ·
Mohammed Al-Ali avatar image Mohammed Al-Ali commented ·
What I want is the same result the you showed in your query but with modifying the Month part to show the results starting from today's date which means it should the results for October, November and December.
0 Likes 0 ·
Mohammed Al-Ali avatar image Mohammed Al-Ali commented ·
I tried your last edited query. It does not work like the one before. I am using the same structure of the database mentioned above but it does not work. It shows me zeros in the Total Number of Taken Quizzes while I have too many data in the database. Your old query works well with me except it does show the results for this month.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.