On 3?26?, ??11?13?, "sans_s...@yahoo.com" <sans_s...@yahoo.com> wrote:
> I thought this was pretty simple but I'm finding it's not. I have 2
> tables, 1 called tStudentResponses and 1 called tTeacherAnswerKey.
>
> All I want to do is take the 10 columns from the tStudentResponses
> table and compare them to the correct answers in the 10 columns in the
> tTeacherAnswerKey table. My thinking was that somehow I could compare
> the 2 columns and then get a count of how many columns did NOT match
> and then use that as the number of answers that were wrong. So, if the
> result set returned 4 records or a count of 4 (however you guys think
> is the better way to write it) then I would have a score of 60% on the
> 10 question test since 4 records were returned as NOT matching.
>
> Am I oversimplifying this???
>
> In "pseudo-code" - this is how I pictured I would do this:
>
> Q1-Q2-Q3.... (Columns)
> C -D - A ... (Answer Key)
> C -B - A ... (Student Answers - resulting the result set showing 1
> answer wrong)
>
> but I do not know how to write the query to generate those results
> I've defined above in the "pseudo-code".
>
> Sorry - I do not have the DDL for you as my machine is throwing an
> error when trying to create the tbl scripts for whatever reason (one
> forum said it was most likely the anti-virus app blowing it up - who
> knows!!!)
>
> Could anyone give me a real world example and the DML for the best way
> to achieve what I'm trying to do here? My apologies if I'm not
> explaining this clearly. If you have any questions on my post please
> let me know and I will respond ASAP as I check the groups multiple
> times each day.
>
> Any help would be greatly appreciated!
some test code here may help you,but i think you may have a bad
design.
below code should be changed according to your exact table columns and
PKs:)
create table #res
(
c1 int,
r1 char(1),
r2 char(1),
r3 char(1)
)
insert into #res
select 1,'a','b','c'
union select 2,'c','d','a'
union select 3,'a','c','d'
create table #key
(
c1 int,
r1 char(1),
r2 char(1),
r3 char(1)
)
insert into #key
select 1,'a','b','d'
select
a.c1,
countrs1 = sum(case when a.r1 = b.r1 then 1 else 0 end)
,countrs2 = sum(case when a.r2 = b.r2 then 1 else 0 end)
,countrs3 = sum(case when a.r3 = b.r3 then 1 else 0 end)
,percentiage = ( sum(case when a.r1 = b.r1 then 1 else 0 end) +
sum(case when a.r2 = b.r2 then 1 else 0 end) + sum(case when a.r3 =
b.r3 then 1 else 0 end) )/ 3.0
from #res a cross join #key b
group by a.c1