|
|
Posted by --CELKO-- on 05/05/06 19:57
I need to find students that have 4 consecutive absences. When a
student is absent 4 times in a row, they can be dropped from the class.
My class attendance file contains each attendance by date and whether
they were present or not. When the student has 4 consecutive value 1
(absent) for a given session and a given class the are considered to be
dropped.
If I needed to know the total number of absences, I know I could group
and summarize, but this one has the consecutive twist.
Table:
The tabel made no sense. NULL student ids of 20 characters in length?
Numeric attendance codes kept in CHAR(), you have no key? etc.
CREATE TABLE ClassAttendance
(class_name CHAR(15) NOT NULL,
student_id CHAR(20) NOT NULL,
session_id CHAR(10) NOT NULL,
meeting_nbr INTEGER NOT NULL,
attend_code INTEGER DEFAULT 1 NOT NULL
CHECK (attend_code IN (1,2,3)),
PRIMARY KEY (class_name, student_id, session_id, meeting_nbr));
Here is a shot using the new OLAP functions:
SELECT DISTINCT student_id, class_name, session_id
FROM (SELECT student_id, class_name, session_id,
SUM(attend_code)
OVER(PARTITION BY class_name, session_id
ORDER BY student_id, class_name, session_id
ROWS 4 PRECEDING)
FROM ClassAttendance
GROUP BY student_id, class_name, session_id)
AS X (student_id, class_name, session_id, last_four)
WHERE last_four = 4;
Navigation:
[Reply to this message]
|