Posted by avode on 03/21/06 09:57

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)

[Back to original message]