Can you please clarify how your tables look like? And how Q9*0.1 = 0.9 ? The weight on Q9 is 0.07 above and the Q9 column in the users table has a value of 7...
I also do not understand your example, but try something like this:
with CTE_Points as
(
select
a.IdUser,
Count(*) as AnsweredQuestions,
Sum(a.Points * q.Weight) as TotalPoints
from Answers as a
join Questions as q on q.IdQuestion = a.IdQuestion
where a.Points >= 0
)
select
u.IdUser,
u.Name,
p.AnsweredQuestions,
p.TotalPoints,
(p.TotalPoints / p.AnsweredQuestions) as AvgPoints
from Users as u
join CTE_Points as p on p.IdUser = u.IdUser
Please check below query, it works fine with data you provided.
declare @table1 table( Q_number int,point int, weight decimal(3,2))
--insert into @table1 select 1,8,0.12 union all
--select 2,-1,0.15 union all
--select 3,-1,0.16 union all
--select 4,-1,0.05 union all
--select 5,-1,0.15 union all
--select 6,-1,0.1 union all
--select 7,-1,0.1 union all
--select 8,-1,0.1 union all
--select 9,7,0.07
insert into @table1 select 1,8,0.1 union all
select 2,-1,0.1 union all
select 3,9,0.1
if ((select SUM(point) from @table1 )!= (select COUNT(Q_number)*(-1)FROM @table1))
begin
select SUM(points*weight),SUM(weight), cast(SUM(points*weight)/SUM(weight) as decimal(5,2))
from (
select Q_number
,points = (case when t.point = -1 then 0 else t.point end)
,weight= (case when t.point = -1 then 0 else t.weight end)
from @table1 as t) changed
end
else
select 'Have no answer'
Thanks.
Please mark the replies as answers if they help or unmark if not.
Feedback to us
vahid.ch
Member
294 Points
331 Posts
help for Questionary Query
Apr 14, 2012 05:09 AM|LINK
Hi there,I'm working in a company and we are going to have a questionary in a few days.
we have 9questions,like this
each question has 0 to 10 point ,and also each one has a weight.
like this
users are enable not to anwer all questions,for instance User A has answered to 2 questions and has not answered to 8 questions which will be -1.
if you look at above table user just answerd to 2 questions I mean Q1 and Q9,
so,it will be calculated as this formula:
(AnsweredQuestions*weight) (Q1*01)+(Q9*0.1) (0.8+0.9)
-----------------------------------------------= ---------------------------= --------------------=8.5
(AnsweredQuestionsweight) (0.1+0.1) (0.2)
so how Can I write this formula in a query?
tnx
mm10
Contributor
6399 Points
1184 Posts
Re: help for Questionary Query
Apr 14, 2012 10:57 AM|LINK
Can you please clarify how your tables look like? And how Q9*0.1 = 0.9 ? The weight on Q9 is 0.07 above and the Q9 column in the users table has a value of 7...
silvioyf
Participant
1754 Points
340 Posts
Re: help for Questionary Query
Apr 14, 2012 12:05 PM|LINK
Hi,
I also do not understand your example, but try something like this:
with CTE_Points as ( select a.IdUser, Count(*) as AnsweredQuestions, Sum(a.Points * q.Weight) as TotalPoints from Answers as a join Questions as q on q.IdQuestion = a.IdQuestion where a.Points >= 0 ) select u.IdUser, u.Name, p.AnsweredQuestions, p.TotalPoints, (p.TotalPoints / p.AnsweredQuestions) as AvgPoints from Users as u join CTE_Points as p on p.IdUser = u.IdUserI hope it's useful.
Chen Yu - MS...
All-Star
21584 Points
2493 Posts
Microsoft
Re: help for Questionary Query
Apr 17, 2012 10:27 AM|LINK
Hi vahid.ch,
Please check below query, it works fine with data you provided.
Thanks.
Feedback to us
Develop and promote your apps in Windows Store