Posted by --CELKO-- on 05/09/06 16:05

Anohter answer: Since there are only a few ranges, we can build an
auxiliary table and use it:

CREATE TABLE FourRanges
(start_session_nbr INTEGER NOT NULL,
end_session_nbr INTEGER NOT NULL,
CHECK (start_session_nbr < end_session_nbr));

INSERT INTO FourRanges VALUES (1, 4);
INSERT INTO FourRanges VALUES (2, 5);
INSERT INTO FourRanges VALUES (3, 6);
INSERT INTO FourRanges VALUES (4, 7);
..
INSERT INTO FourRanges VALUES (9, 12);

SELECT A1.course_name, A1.session_id, A1.student_id,
SUM(attend_code)
FROM ClassAttendance AS A1, FourRanges AS F
WHERE A1.session_nbr BETWEEN F.start_session_nbr
AND F.end_session_nbr
GROUP BY course_name, session_id, student_id
HAVING SUM(attend_code) = 4;

• Next in forum: Running SQL and Oracle on the same server
• Prev in forum: Re: Need Emergency Advice
• Thread view: Re: how to find conditions across rows (attendance)

[Reply to this message]