如何显示最近三个月的结果?

编程入门行业动态更新时间:2024-10-23 17:23:53

本文介绍了如何显示最近三个月的结果?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我正在开发一个基于网络的培训管理系统，该系统将向管理层显示每个部门的培训记录.我的数据库设计如下:

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:

员工表:用户名、姓名、工作、部门 ID

Employee Table: Username, Name, Job, DivisionID

部门表:部门ID、部门名称

Division Table: DivisionID, DivisionName

测验表:测验ID、标题、描述

Quiz Table: QuizID, Title, Description

UserQuiz 表:UserQuizID、Score、DateTimeComplete、QuizID、用户名

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

注意:每个表中的第一个属性是主键.

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

我用于此任务的 SQL 查询是:

The SQL Query that I am using for this task 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)

此查询将显示每个部门按月参加的测验总数.我现在想要的是显示过去三个月的这些结果.另外，即使有不参加任何测验的部门，我也想显示所有部门.这意味着我想显示参加测验次数为零的部门.

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.

推荐答案

为了显示 ALL Divisions，需要在列表的第一个，然后 LEFT JOINed 到计数结果.您需要 COALESCE() 值，因此如果为空，则将返回零.对于过去 3 个月，您只需为日期范围添加 WHERE 子句即可.

For showing ALL Divisions, that will need to be first in the list, then LEFT JOINed to the count results. You'll need to COALESCE() the value so if null, will return the zero instead. As for the last 3 months, you'll just need to add a WHERE clause for the date range.

根据评论编辑....我更进一步，对于每个部门，我将其创建为交叉表，以显示带有 3 列的部门，在一行中显示每个月的计数......因此，结果列名称将是相同的，但会反映实际的月份数据内容...实际上调整了 WHERE 子句只回溯了 2 个月...回溯 2 个月加上当前等于 3 个月.

Edit... per comment. I've taken it one step further and for each division, I've created it as a Cross-Tab to show the division with 3 columns showing the counts of each respective month in a single row... So, the resulting column names would be the same, but would reflect the actual month data content... Actually adjusted the WHERE clause going back only 2 months... 2 months back plus current equals 3 months total.

select d.DivisionName, SUM( case when PreQuery.ByMonth = DATENAME(Month, DateAdd( month, -2, GetDate()) then PreQuery.DistinctQuizes else 0 end ) as TwoMonthsAgoCount, SUM( case when PreQuery.ByMonth = DATENAME(Month, DateAdd( month, -1, GetDate()) then PreQuery.DistinctQuizes else 0 end ) as OneMonthAgoCount, SUM( case when PreQuery.ByMonth = DATENAME(Month, GetDate()) then PreQuery.DistinctQuizes else 0 end ) as CurrentMonthCount from Divisions d left join ( select count( distinct UQ.QuizID ) DistinctQuizes, DATENAME(Month, UQ.DateTimeComplete) ByMonth, d2.DivisionName from UserQuiz UQ JOIN Quiz Q on UQ.QuizID = Q.QuizID JOIN Employee E on UQ.UserName = E.UserName JOIN Divisions D2 on E.DivisionCode = D2.SapCode where UQ.DateTimeComplete between DateAdd( month, -2, GetDate()) and GetDate() group by d2.DivisionName, DATENAME(Month, UQ.DateTimeComplete) ) PreQuery ON d.DivisionName = PreQuery.DivisionName GROUP BY d.DivisionName

通过使用GetDate()"，这将返回计算机上的当前日期.这将是结束日期.第一个日期将是基本的日期算术...使用 DateAdd 函数，根据当前日期添加一个基于 -2 个月的间隔(负数表示向后).

By using "GetDate()", this returns whatever the current date is on the computer. This would be the ENDING date. The first date would be basic date arithmetic... Use the DateAdd function, add an interval based on months of -2 (negative to go BACKWARDS), based on whatever the current date is.