Posted by Tom Moreau on 03/19/06 22:28

Please post your DDL + INSERT statements of your sample data. Here's an
untested solution:

select
TheDate
from
MyTable
where
Grade in ('A', 'B')
and Student in ('Student1', 'Student2')
group by
TheDate
having
(sum (case when Grade = 'A' and Student = 'Student1' then 1 else 0
end) = 1
and sum (case when Grade = 'B' and Student = 'Student2' then 1 else 0
end) = 1)
or (sum (case when Grade = 'A' and Student = 'Student2' then 1 else 0
end) = 1
and sum (case when Grade = 'B' and Student = 'Student1' then 1 else 0
end) = 1)

Assumes only one grade per student per date.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"肥權" <lokalun@gmail.com> wrote in message
news:1142790320.623759.278150@g10g2000cwb.googlegroups.com...
Hi everybody,

I have a table storing the test dates, student names and the grade of
the students. I am now going to find out those dates with student1 got
A and student2 got B and vice versa (i.e. student1 got B and student2
got A). How can I do that? I think it is quite complicate for me.
Please help. I'm using SQL server 2000.

FRANKLIN

[Back to original message]