sql server 2008 r2 - How to modify this SQL Query to show the percentage of participation in the last sending quiz in each division in the company? -
i have following query shows number of participants in last sending quiz in each division. want keep instead of showing number of participants, want show percentage of participation in last sending quiz.
for information, have following database design:
employee table:
username, name, job, divisioncodedivision table:
sapcode, divisionnamequiz table:
quizid, title, description, issentuserquiz table:
userquizid, score, datetimecomplete, quizid, username
note: first attribute in each table primary key.
issent flag used determine quiz sent users , 1 not.
so how modify query show percentage of participation?
sql query:
select dbo.divisions.divisionshortcut, count(distinct dbo.userquiz.username) [number of participants], dbo.quiz.quizid dbo.divisions inner join dbo.employee on dbo.divisions.sapcode = dbo.employee.divisioncode inner join dbo.userquiz on dbo.employee.username = dbo.userquiz.username inner join dbo.quiz on dbo.userquiz.quizid = dbo.quiz.quizid [quiz].[quizid] = (select max([quizid]) dbo.quiz (issent = 1)) group dbo.divisions.divisionshortcut, dbo.quiz.quizid database schema:
/****** object: table [dbo].[divisions] script date: 03/07/2012 15:48:13 ******/ 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 /****** object: table [dbo].[quiz] script date: 03/07/2012 15:48:13 ******/ 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 /****** object: table [dbo].[employee] script date: 03/07/2012 15:48:13 ******/ 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 /****** object: table [dbo].[userquiz] script date: 03/07/2012 15:48:13 ******/ 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 /****** object: default [df_quiz_issent] script date: 03/07/2012 15:48:13 ******/ alter table [dbo].[quiz] add constraint [df_quiz_issent] default ((0)) [issent] go /****** object: foreignkey [fk_employee_divisions] script date: 03/07/2012 15:48:13 ******/ alter table [dbo].[employee] 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: 03/07/2012 15:48:13 ******/ alter table [dbo].[userquiz] 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: 03/07/2012 15:48:13 ******/ alter table [dbo].[userquiz] check add constraint [fk_userquiz_quiz] foreign key([quizid]) references [dbo].[quiz] ([quizid]) go alter table [dbo].[userquiz] check constraint [fk_userquiz_quiz]
assuming participation percentage of total users, try:
select dbo.divisions.divisionshortcut, count(distinct dbo.userquiz.username) [number of participants], count(distinct dbo.userquiz.username) * 100 / max([allusers].[usercount]) [percent participation], dbo.quiz.quizid (select count(*) [usercount] dbo.employee) [allusers] cross join dbo.divisions inner join dbo.employee on dbo.divisions.sapcode = dbo.employee.divisioncode inner join dbo.userquiz on dbo.employee.username = dbo.userquiz.username inner join dbo.quiz on dbo.userquiz.quizid = dbo.quiz.quizid [quiz].[quizid] = (select max([quizid]) dbo.quiz (issent = 1)) group dbo.divisions.divisionshortcut, dbo.quiz.quizid edit: allowing users able answer quiz more once, should able run following query return participation percentage of users @ departmental level:
select divisionshortcut, max(numberparticipants) - max(nullparticipant) [number of participants], (max(numberparticipants) - max(nullparticipant)) * 100 / max(divisionemployees) [percent participation], max(latestquiz) latestquiz (select d.divisionshortcut, dense_rank() on (partition d.divisionshortcut order uq.username) numberparticipants, case when uq.username null 1 else 0 end nullparticipant, dense_rank() on (partition d.divisionshortcut order e.username) divisionemployees, lq.latestquiz (select max([quizid]) latestquiz dbo.quiz (issent = 1)) lq cross join dbo.divisions d inner join dbo.employee e on d.sapcode = e.divisioncode left join dbo.userquiz uq on e.username = uq.username , lq.latestquiz = uq.quizid) sq group divisionshortcut
Comments
Post a Comment