Most efficient solution - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Most efficient solution

Author

Topic

jess
Starting Member

17 Posts

Posted - 2006-12-13 : 11:39:37

So I am looking for the most efficient way of solving my current issue.
I've tried to create a simplified version of the problem here.
Lets say I have a table that stores surveys that people have filled out. Each survey is stored as 1 line in the table which contains the survey number and the results of 10 questions, which can be Yes (Y) No (N) or Null (blank). So an example of the table would be:

Survey# Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10
101 Y _ Y Y Y Y Y Y Y Y
102 N Y Y N Y _ _ _ Y N
103 Y N _ N Y Y N N Y _

Here's what I need to find out: What percentage of these surveys "Pass"
In order to "Pass" a survey must have 75% Yes answers out of all the questions that they answered (not necessarily out of 10 because they don't have to answer all questions). So if they only answered 8 of the 10 questions then they need to have 6 or more of the 8 be Yes in order to Pass.

So using the example table above:
Survey 101 has 9 Yes's out of 9 answers = 100% = Pass
Survey 102 has 4 Yes's out of 7 answers = 57% = Not Pass
Survey 102 has 4 Yes's out of 8 answers = 50% = Not Pass

So 1 out of 3 surveys passed so the end result would be:
33.3% of the surveys Passed
66.7 fo the surveys did not Pass.

Can anyone help me find an efficient solution? I cannot seem to come up with a very simple solution and I know there's got to be one out there.

Thanks!
Jess

P.S. I'm running SQL Server 2000 and I'll paste some sample code here to create the example table I mentioned above:

DECLARE @surveys TABLE
(
survey_num int,
q1 bit,
q2 bit,
q3 bit,
q4 bit,
q5 bit,
q6 bit,
q7 bit,
q8 bit,
q9 bit,
q10 bit
)

INSERT INTO @surveys
VALUES (101, 1, null, 1, 1, 1, 1, 1, 1, 1, 1)

INSERT INTO @surveys
VALUES (102, 0, 1, 1, 0, 1, null, null, null, 1, 0)

INSERT INTO @surveys
VALUES (103, 1, 0, null, 0, 1, 1, 0, 0, 1, null)

SELECT *
FROM @surveys

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-12-13 : 13:08:45

Make you life easier and normalize your data. Anyway ...


select
    (sum(case when PassFail = 'Pass' then 1 else 0 end) * 1.0) / count(*) as 'PassPercentage',
    (sum(case when PassFail = 'NotPass' then 1 else 0 end) * 1.0) / count(*) as 'NotPassPercentage'
from
   (select
        survey_num,
        sum(case when q is not null then 1 else 0 end) as 'NumberAnswered',
        sum(case when q = 1 then 1 else 0 end) as 'AnsweredYes',
        case
            when (sum(case when q = 1 then 1 else 0 end) * 1.0) / (sum(case when q is not null then 1 else 0 end) * 1.0) > .75 then 'Pass'
            else 'NotPass'
        end as 'PassFail'
    from
       (select survey_num, q1 as q from @surveys
        union all
        select survey_num, q2 as q from @surveys
        union all
        select survey_num, q3 as q from @surveys
        union all
        select survey_num, q4 as q from @surveys
        union all
        select survey_num, q5 as q from @surveys
        union all
        select survey_num, q6 as q from @surveys
        union all
        select survey_num, q7 as q from @surveys
        union all
        select survey_num, q8 as q from @surveys
        union all
        select survey_num, q9 as q from @surveys
        union all
        select survey_num, q10 as q from @surveys) s
    group by
        survey_num) sall

Jay
to here knows when

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 13:52:16

Look how simple the query is, when the data is normalized

-- prepare test data
declare @surveys table (survey_num int, q tinyint, pass bit)

insert	@surveys 
select	101, 1, 1 union all
select	101, 3, 1 union all
select	101, 4, 1 union all
select	101, 5, 1 union all
select	101, 6, 1 union all
select	101, 7, 1 union all
select	101, 8, 1 union all
select	101, 9, 1 union all
select	101, 10, 1 union all
select	102, 1, 0 union all
select	102, 2, 1 union all
select	102, 3, 1 union all
select	102, 4, 0 union all
select	102, 5, 1 union all
select	102, 9, 1 union all
select	102, 10, 0 union all
select	103, 1, 1 union all
select	103, 2, 0 union all
select	103, 4, 0 union all
select	103, 5, 1 union all
select	103, 6, 1 union all
select	103, 7, 0 union all
select	103, 8, 0 union all
select	103, 9, 1

-- do the work
select		survey_num,
		max(case when q = 1 then 1 * pass end) q1,
		max(case when q = 2 then 1 * pass end) q2,
		max(case when q = 3 then 1 * pass end) q3,
		max(case when q = 4 then 1 * pass end) q4,
		max(case when q = 5 then 1 * pass end) q5,
		max(case when q = 6 then 1 * pass end) q6,
		max(case when q = 7 then 1 * pass end) q7,
		max(case when q = 8 then 1 * pass end) q8,
		max(case when q = 9 then 1 * pass end) q9,
		max(case when q = 10 then 1 * pass end) q10,
		case when 1.0 * sum(1 * pass) / count(*) >= .75 then 1 else 0 end [pass]
from		@surveys
group by	survey_num
order by	survey_num

select		1.0 * sum([pass]) / count(*) [pass],
		1.0 * (count(*) - sum([pass])) / count(*) [not pass]
from		(
			select		survey_num,
					case when 1.0 * sum(1 * pass) / count(*) >= .75 then 1 else 0 end [pass]
			from		@surveys
			group by	survey_num
		) q

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 14:01:18

Without all these UNION ALLs and only 1 table scan compared to 10 table scans.

-- prepare test data
declare @surveys table (survey_num int, q1 bit, q2 bit, q3 bit, q4 bit, q5 bit, q6 bit, q7 bit, q8 bit, q9 bit, q10 bit)

insert	@surveys
select	101, 1, null, 1, 1, 1, 1, 1, 1, 1, 1 union all
select	102, 0, 1, 1, 0, 1, null, null, null, 1, 0 union all
select	103, 1, 0, null, 0, 1, 1, 0, 0, 1, null

-- do the work
select		sum(case when [yes] / [answered] >= .75 then 1.0 else .0 end) / count(*) [pass],
		sum(case when [yes] / [answered] >= .75 then .0 else 1.0 end) / count(*) [not pass]
from		(
			select		survey_num,
					sign(isnull(1 + q1, 0)) + sign(isnull(1 + q2, 0)) + sign(isnull(1 + q3, 0)) + sign(isnull(1 + q4, 0)) + sign(isnull(1 + q5, 0)) +
					sign(isnull(1 + q6, 0)) + sign(isnull(1 + q7, 0)) + sign(isnull(1 + q8, 0)) + sign(isnull(1 + q9, 0)) + sign(isnull(1 + q10, 0)) [answered],
					sign(isnull(q1, 0)) + sign(isnull(q2, 0)) + sign(isnull(q3, 0)) + sign(isnull(q4, 0)) + sign(isnull(q5, 0)) +
					sign(isnull(q6, 0)) + sign(isnull(q7, 0)) + sign(isnull(q8, 0)) + sign(isnull(q9, 0)) + sign(isnull(q10, 0)) [yes]
			from		@surveys
		 ) q

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 14:46:43

Strange system of passed questions. In order to success, you only have to answer 1 question correctly and leave the rest. This way, I get 100% on the survey...

Peter Larsson
Helsingborg, Sweden

jess
Starting Member

17 Posts

Posted - 2006-12-13 : 16:17:23

Thank you both for your posts as this has been extremely helpful to me. It looks like all 3 solutions function well.

Page47 - Thank you for your response and you get a 99% for this project -- The only thing I noticed was your line that said "> .75 then 'Pass'" should be ">= .75 the 'Pass'" because it marks surveys of 75% as Not Passing otherwise. But anyway, brilliant response and you had me looking into the normalizing thing. If this wasn't a big nasty project I inherited that isn't worth putting tons of work into I'd be all over that...

Peso - I appreciate that you listed both queries as it helped me to learn a lot. Now the fun part will be plugging one of these solutions you guys gave me into my actual project as that is much bigger and uglier than the simplified version I gave you!! The concept is identical though so you've been a tremendous help.

Also, in response to your comment that it is odd how a survey is declared passing (answer 1 question yes and leave the rest blank to pass) you're right that it is odd, but this application isn't for a quiz or anything where the person taking the quiz gets graded. My "real world" version of this problem is that clinicians here where I work have to review other clinician's work once in a while for quality assurance. So clinician A will fill out a survey regarding the work clinician B has done. The fill out a form with somewhere around 100 questions asking everything from "was the Consent to Treat form signed by the client of clinician B" to "did clinician B record the client's symptoms in the document" to "was the client's referral source recorded." Depending on the type of review they are doing, they don't have to fill out certain sections, so they may only have to answer 80 out of 100 questions for example. Therefore if clinician A only has to fill out 70 of the questions for the review, and they say clinician B did everything correctly on 69 of the 70 items it will grade it that clinician B did an acceptable job in that case. Also, they have to do better than 75%, that was just a number I picked for my simplified version. Also there are other cases where if certain questions are answered no then the review automatically gets marked as unacceptable!

Well I look forward to frequenting this forum more often and I think I have a lot to learn from all of you.

Thanks again!
Jess

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources