English 中文(简体)
如何每月显示部门一级的参与率,并比较每个司?
原标题：How to show the % participation on a monthly basis at department level and compare each division?

我正在开发一个网吧。 这一应用为用户提供了qui。 现在,我需要为管理层制定一个强有力和有意义的仪表板。 仪表板必须显示许多统计数据。 他们希望我以这样的方式设计这一仪表板:显示参与率=单位数,按每月按部一级计算的总静.数进行区分,并对各司进行比较。

问题是:我有以下数据库设计:

www.un.org/Depts/DGACM/index_spanish.htm Employee Table:username, name, Job, DivisionID

<>Division Table: DivisionID, DivisionName

www.un.org/Depts/DGACM/index_spanish.htm Quiz 表:QuzID, 标题,说明

www.un.org/Depts/DGACM/index_spanish.htm 用户数据表:用户数据,分数,日期Complete,QuzID,用户名

NOTE:每个表格中的第一个属性是主要的关键。

我试图用它来显示过去三个月的结果,即:

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)

此外,我还尝试了许多问询,每次都与我想要的东西不同。 坦率地说,我对如何达到上述要求没有任何想法。

EDIT: Here is the schema with some data:

    /****** Object:  Table [dbo].[Divisions]    Script Date: 01/15/2012 12:29:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Divisions](
    [SapCode] [float] NOT NULL,
    [DivisionShortcut] [varchar](10) NOT NULL,
    [DivisionName] [varchar](max) NOT NULL,
 CONSTRAINT [PK_Divisions] PRIMARY KEY CLUSTERED 
(
    [SapCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Divisions] ([SapCode], [DivisionShortcut], [DivisionName]) VALUES (30003143, N PMOD , N AB )
INSERT [dbo].[Divisions] ([SapCode], [DivisionShortcut], [DivisionName]) VALUES (30003144, N ESD , N BC )
INSERT [dbo].[Divisions] ([SapCode], [DivisionShortcut], [DivisionName]) VALUES (30003153, N PESD , N CD )
INSERT [dbo].[Divisions] ([SapCode], [DivisionShortcut], [DivisionName]) VALUES (30003158, N SSD , N DE )
INSERT [dbo].[Divisions] ([SapCode], [DivisionShortcut], [DivisionName]) VALUES (30012601, N PEOD , N EF )
INSERT [dbo].[Divisions] ([SapCode], [DivisionShortcut], [DivisionName]) VALUES (30021812, N PEMD , N FG )
INSERT [dbo].[Divisions] ([SapCode], [DivisionShortcut], [DivisionName]) VALUES (30021876, N BAG , N GH
 )
INSERT [dbo].[Divisions] ([SapCode], [DivisionShortcut], [DivisionName]) VALUES (30023176, N EPM , N HI )
/****** Object:  Table [dbo].[Quiz]    Script Date: 01/15/2012 12:29:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Quiz](
    [QuizID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [varchar](max) NOT NULL,
    [IsSent] [bit] NOT NULL,
    [Description] [varchar](max) NULL,
 CONSTRAINT [PK_Quiz] PRIMARY KEY CLUSTERED 
(
    [QuizID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Quiz] ON
INSERT [dbo].[Quiz] ([QuizID], [Title], [IsSent], [Description]) VALUES (11, N Safety Quiz 1 , 0, N General Safety Quiz )
INSERT [dbo].[Quiz] ([QuizID], [Title], [IsSent], [Description]) VALUES (12, N Safety Quiz 2 , 0, N General Safety Quiz )
INSERT [dbo].[Quiz] ([QuizID], [Title], [IsSent], [Description]) VALUES (13, N Safety Quiz 3 , 0, N TEST )
INSERT [dbo].[Quiz] ([QuizID], [Title], [IsSent], [Description]) VALUES (14, N Safety Quiz 4 , 0, N TEST )
SET IDENTITY_INSERT [dbo].[Quiz] OFF
/****** Object:  Table [dbo].[employee]    Script Date: 01/15/2012 12:29:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[employee](
    [Name] [nvarchar](max) NOT NULL,
    [Username] [nvarchar](255) NOT NULL,
    [JobTitle] [nvarchar](max) NOT NULL,
    [BadgeNo] [float] NOT NULL,
    [EmpOrgType] [float] NOT NULL,
    [DivisionCode] [float] NOT NULL,
 CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED 
(
    [Username] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[employee] ([Name], [Username], [JobTitle], [BadgeNo], [EmpOrgType], [DivisionCode]) VALUES (N John , N AGUILEBS , N Engineering Technician , 9545246, 2, 30012601)
INSERT [dbo].[employee] ([Name], [Username], [JobTitle], [BadgeNo], [EmpOrgType], [DivisionCode]) VALUES (N TED , N ALKHATHI , N Technical Clk Engrg , 8016951, 2, 30012601)
INSERT [dbo].[employee] ([Name], [Username], [JobTitle], [BadgeNo], [EmpOrgType], [DivisionCode]) VALUES (N Bel , N ALMARHMS , N Business Sys Analyst Iv , 289589, 1, 30012601)
INSERT [dbo].[employee] ([Name], [Username], [JobTitle], [BadgeNo], [EmpOrgType], [DivisionCode]) VALUES (N Tony , N GRIGFW0A , N Business Sys Analyst I , 9395990, 2, 30012601)
INSERT [dbo].[employee] ([Name], [Username], [JobTitle], [BadgeNo], [EmpOrgType], [DivisionCode]) VALUES (N Maria , N KHWAILAM , N Asst Engineer Ii , 431177, 2, 30012601)
INSERT [dbo].[employee] ([Name], [Username], [JobTitle], [BadgeNo], [EmpOrgType], [DivisionCode]) VALUES (N Johny , N SALEMS0M , N Business Sys Analyst Iii , 431163, 2, 30003143)
INSERT [dbo].[employee] ([Name], [Username], [JobTitle], [BadgeNo], [EmpOrgType], [DivisionCode]) VALUES (N Teddy , N TOWAAH0A , N Business Sys Analyst Iv , 8819001, 2, 30003143)
INSERT [dbo].[employee] ([Name], [Username], [JobTitle], [BadgeNo], [EmpOrgType], [DivisionCode]) VALUES (N Arnold , N VILLAV0A , N Asst Engineer I , 329398, 1, 30023176)
/****** Object:  Table [dbo].[UserQuiz]    Script Date: 01/15/2012 12:29:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserQuiz](
    [UserQuizID] [int] IDENTITY(1,1) NOT NULL,
    [QuizID] [int] NOT NULL,
    [DateTimeComplete] [smalldatetime] NOT NULL,
    [Score] [float] NOT NULL,
    [Username] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_UserQuiz] PRIMARY KEY CLUSTERED 
(
    [UserQuizID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[UserQuiz] ON
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (21, 11, CAST(0x9FCD0345 AS SmallDateTime), 0, N ALMARHMS )
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (81, 11, CAST(0x9FD50288 AS SmallDateTime), 0, N ALMARHMS )
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (82, 11, CAST(0x9FC80000 AS SmallDateTime), 100, N TOWAAH0A )
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (83, 12, CAST(0x9FCE0000 AS SmallDateTime), 100, N ALMARHMS )
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (84, 11, CAST(0x9E790000 AS SmallDateTime), 50, N VILLAV0A )
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (85, 12, CAST(0x9FC20000 AS SmallDateTime), 100, N ALMARHMS )
SET IDENTITY_INSERT [dbo].[UserQuiz] OFF
/****** Object:  Default [DF_Quiz_IsSent]    Script Date: 01/15/2012 12:29:48 ******/
ALTER TABLE [dbo].[Quiz] ADD  CONSTRAINT [DF_Quiz_IsSent]  DEFAULT ((0)) FOR [IsSent]
GO
/****** Object:  ForeignKey [FK_employee_Divisions]    Script Date: 01/15/2012 12:29:48 ******/
ALTER TABLE [dbo].[employee]  WITH CHECK ADD  CONSTRAINT [FK_employee_Divisions] FOREIGN KEY([DivisionCode])
REFERENCES [dbo].[Divisions] ([SapCode])
GO
ALTER TABLE [dbo].[employee] CHECK CONSTRAINT [FK_employee_Divisions]
GO
/****** Object:  ForeignKey [FK_UserQuiz_employee]    Script Date: 01/15/2012 12:29:48 ******/
ALTER TABLE [dbo].[UserQuiz]  WITH CHECK ADD  CONSTRAINT [FK_UserQuiz_employee] FOREIGN KEY([Username])
REFERENCES [dbo].[employee] ([Username])
GO
ALTER TABLE [dbo].[UserQuiz] CHECK CONSTRAINT [FK_UserQuiz_employee]
GO
/****** Object:  ForeignKey [FK_UserQuiz_Quiz]    Script Date: 01/15/2012 12:29:48 ******/
ALTER TABLE [dbo].[UserQuiz]  WITH CHECK ADD  CONSTRAINT [FK_UserQuiz_Quiz] FOREIGN KEY([QuizID])
REFERENCES [dbo].[Quiz] ([QuizID])
GO
ALTER TABLE [dbo].[UserQuiz] CHECK CONSTRAINT [FK_UserQuiz_Quiz]
GO
最佳回答

引证:

SELECT 
    COUNT(DISTINCT dbo.employee.UserName) UserCount,
    (SELECT COUNT(*) FROM dbo.Quiz) AS [Total Number of Quizzes],  
    (SELECT COUNT(*) FROM dbo.Quiz)*COUNT(DISTINCT dbo.employee.UserName) AS [Total Number of Quizzes for All users],  
    dbo.Divisions.DivisionName,
    DATENAME(Month, dbo.UserQuiz.DateTimeComplete) AS MONTH,
    COUNT(DISTINCT dbo.UserQuiz.QuizId)*COUNT(DISTINCT dbo.employee.UserName) AS [Completed This Month],
    COUNT(DISTINCT dbo.UserQuiz.QuizId)*100/(SELECT COUNT(*) FROM dbo.Quiz) AS [Percent Completion]
FROM dbo.employee
JOIN  dbo.Divisions ON dbo.employee.DivisionCode = dbo.Divisions.SapCode
LEFT JOIN dbo.UserQuiz ON dbo.UserQuiz.Username = dbo.employee.Username 
WHERE dbo.UserQuiz.DateTimeComplete IS NOT NULL
GROUP BY 
    dbo.Divisions.DivisionName, 
    DATENAME(Month, dbo.UserQuiz.DateTimeComplete)

www.un.org/Depts/DGACM/index_french.htm

SELECT 
    dbo.Divisions.DivisionName,
    DATENAME(Month, dbo.UserQuiz.DateTimeComplete) AS MONTH,
    COUNT(DISTINCT dbo.UserQuiz.QuizId)*COUNT(DISTINCT dbo.employee.UserName)*100/((SELECT COUNT(*) FROM dbo.Quiz)*COUNT(DISTINCT dbo.employee.UserName)) AS [Percent Completion]
FROM dbo.employee
JOIN  dbo.Divisions ON dbo.employee.DivisionCode = dbo.Divisions.SapCode
LEFT JOIN dbo.UserQuiz ON dbo.UserQuiz.Username = dbo.employee.Username 
WHERE 
    dbo.UserQuiz.DateTimeComplete IS NOT NULL
AND DATEPART(MONTH, dbo.UserQuiz.DateTimeComplete) = 12
GROUP BY 
    dbo.Divisions.DivisionName, 
    DATENAME(Month, dbo.UserQuiz.DateTimeComplete)
时间：2012-01-15 08:35:25
问题回答

暂无回答


上一篇：
下一篇：


相关问题
SQL SubQuery getting particular column

I noticed that there were some threads with similar questions, and I did look through them but did not really get a convincing answer. Here s my question: The subquery below returns a Table with 3 ...

时间：2009-11-13 05:35:38 回答：3 展示：1
难以执行 REGEXP_SUBSTR

I m 查询Oracle 10g。 我有两张表格(样本数据见下文)。 i m 试图提取一些领域

时间：2009-11-13 04:45:17 回答：1 展示：1
SQL Query Shortcuts

What are some cool SQL shorthands that you know of? For example, something I learned today is you can specify to group by an index: SELECT col1, col2 FROM table GROUP BY 2 This will group by col2

时间：2009-11-13 04:22:07 回答：3 展示：1
PHP array callback functions for cleaning output

I have an array of output from a database. I am wondering what the cleanest way to filter the values is example array Array ( [0] => Array ( [title] => title 1 ...

时间：2009-11-09 15:35:56 回答：5 展示：1
OracleParameter and DBNull.Value

we have a table in an Oracle Database which contains a column with the type Char(3 Byte). Now we use a parameterized sql to select some rows with a DBNull.Value and it doesn t work: OracleCommand ...

时间：2009-11-09 14:37:41 回答：5 展示：1
Running numbers in SQL

I have a SQL-statement like this: SELECT name FROM users WHERE deleted = 0; How can i create a result set with a running number in the first row? So the result would look like this: 1 Name_1 2 ...

时间：2009-11-09 14:33:30 回答：3 展示：1
How to get SQL queries for each user where env is production

I’m developing an application dedicated to generate statistical reports, I would like that user after saving their stat report they save sql queries too. To do that I wrote the following module: ...

时间：2009-11-09 14:20:51 回答：1 展示：1
热门标签

友情链接

Allggapp Alljchome-教程家园 mvfinale.com-影视剧情大结局大全