如何显示最近三个月的结果? [英] How to show the results for the last three months?

问题描述

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

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.

这篇关于如何显示最近三个月的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！