# help for Questionary Query RSS

## 3 replies

Last post Apr 17, 2012 10:27 AM by Chen Yu - MSFT

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

Questuion-------------------------------Point--------------------------weight

Q1             0----- 1----- 2-----3----- 4-----5-----6-----7-----8-----9----- 10         0.12
Q2             0----- 1----- 2-----3----- 4-----5-----6-----7-----8-----9----- 10         0.15
Q3             0----- 1----- 2-----3----- 4-----5-----6-----7-----8-----9----- 10         0.16
Q4             0----- 1----- 2-----3----- 4-----5-----6-----7-----8-----9----- 10         0.05
Q5             0----- 1----- 2-----3----- 4-----5-----6-----7-----8-----9----- 10         0.15
Q6             0----- 1----- 2-----3----- 4-----5-----6-----7-----8-----9----- 10         0.1
Q7             0----- 1----- 2-----3----- 4-----5-----6-----7-----8-----9----- 10         0.1
Q8             0----- 1----- 2-----3----- 4-----5-----6-----7-----8-----9----- 10         0.1
Q9             0----- 1----- 2-----3----- 4-----5-----6-----7-----8-----9----- 10         0.07

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.

Q1          Q2          q3          q4          Q5          q6          Q7          q8          q9
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
8           -1          -1          -1          -1          -1          -1          -1          7

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

• Edited by vahid.ch on Apr 14, 2012 05:10 AM
• Edited by vahid.ch on Apr 14, 2012 05:12 AM
• Edited by vahid.ch on Apr 14, 2012 05:13 AM
• Edited by vahid.ch on Apr 14, 2012 05:14 AM
• Edited by vahid.ch on Apr 14, 2012 05:14 AM
• Edited by vahid.ch on Apr 14, 2012 05:15 AM

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...

Please 'Mark as Answer' if this post helped you.

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.IdUser

I hope it's useful.

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.

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

Develop and promote your apps in Windows Store