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.**
Please provide us some sample data and desired output to get a certain answer.
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.
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
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?
Yes, while grouping the data by month, you should take year into consideration as well.
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)
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?***
Are you saying you just want to see the data for months only? You do not need Divisions in the output?
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.
OK, I get it. I have changed it in my original answer. Only the declaration part was needed to be changed.
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.
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.
