Query Problem

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.

FRANKLINPlease 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.googlegr oups.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|||Another alternative, again untested:

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
when Grade = 'B' and Student = 'Student2' then 1
else 0 end) = 2
or sum (case
when Grade = 'A' and Student = 'Student2' then 1
when Grade = 'B' and Student = 'Student1' then 1
else 0 end) = 2

Same assumption as before.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:najTf.1845$ji6.54932@.news20.bellglobal.com...
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.googlegr oups.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|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Here is my guess

CREATE TABLE Gradebook
(student_name CHAR(15) NOT NULL,
test_date DATETIME NOT NULL,
grade CHAR(1) NOT NULL,
PRIMARY KEY (student_name, test_date));

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

untested

WITH X(student_name, grade, test_date)
SELECT student_name, grade, test_date
FROM Gradebook
WHERE student_name IN ('student1', 'student2')
AND grade IN ('A', 'B')
SELECT
FROM X AS X1, X AS X2
WHERE X1.test_date = X2.test_date
AND X1.student_name <> X2.student_name
AND X1.grade <> X2.grade;|||FRANKLIN,

i repeat the sentence about the necessity to post ddl and sample data
and want to suggest the following solution of your problem:

CREATE TABLE Gradebook
(student_name CHAR(15) NOT NULL,
test_date DATETIME NOT NULL,
grade CHAR(1) NOT NULL,
PRIMARY KEY (student_name, test_date));

INSERT INTO Gradebook
SELECT 'Student1', '2006-01-01', 'A' UNION ALL
SELECT 'Student2', '2006-01-01', 'B' UNION ALL
SELECT 'Student3', '2006-01-01', 'A' UNION ALL
SELECT 'Student1', '2006-01-03', 'C' UNION ALL
SELECT 'Student2', '2006-01-03', 'A' UNION ALL
SELECT 'Student3', '2006-01-03', 'B' UNION ALL
SELECT 'Student1', '2006-01-07', 'A' UNION ALL
SELECT 'Student2', '2006-01-07', 'C' UNION ALL
SELECT 'Student3', '2006-01-07', 'A' UNION ALL
SELECT 'Student1', '2006-01-09', 'A' UNION ALL
SELECT 'Student2', '2006-01-09', 'B';

SET STATISTICS IO ON;

-- SELECT DISTINCT G1.test_date
-- FROM Gradebook AS G1, Gradebook AS G2
-- WHERE G1.test_date = G2.test_date
-- AND ( ( G1.student_name = 'Student1' AND G1.grade = 'A'
-- AND G2.student_name = 'Student2' AND G2.grade = 'B')
-- OR( G1.student_name = 'Student2' AND G1.grade = 'B'
-- AND G2.student_name = 'Student1' AND G2.grade = 'A'));

SELECT G1.test_date
FROM Gradebook AS G1
WHERE G1.student_name = 'Student1'
AND G1.grade IN('A', 'B')
AND EXISTS(SELECT *
FROM Gradebook AS G2
WHERE G2.student_name = 'Student2'
AND G2.test_date = G1.test_date
AND G2.grade = CASE G1.grade
WHEN 'A' THEN 'B'
WHEN 'B' THEN 'A' END);

SET STATISTICS IO OFF;

DROP TABLE Gradebook;

--
Andrey Odegov
avodeGOV@.yandex.ru
(remove GOV to respond)|||homework.