how to find conditions across rows (attendance)

SQL Apprentice Question
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:


CREATE TABLE "dbo"."clsatt"
("FULL_CLASS_ID" CHAR(15) NOT NULL,
"STUDENT_ID" CHAR(20) NULL,
"SESSION_ID" CHAR(10) NULL,
"MEETING" SMALLINT NOT NULL,
"PRESENT" CHAR(2) NOT NULL)
;
Present value of 1 is absent, value of 2 is present (3 means holiday)
Classes typically meet 12 times.
I would want something like
FULL_CLASS_ID, STUDENT_ID, SESSION_ID, 'Dropped'
as the output.


Notice in the example the first student was absent the last 4 meetings
The second student 5 absenses
and the third student was totally absent
In these three examples, they are flagged as dropped.
TIA
Rob
Inserts:


---------------------------------------------------------------------------­------
insert into clsatt values ('BUS100','1675812194','200203',1,'2')
insert into clsatt values ('BUS100','1675812194','200203',2,'2')
insert into clsatt values ('BUS100','1675812194','200203',3,'2')
insert into clsatt values ('BUS100','1675812194','200203',4,'2')
insert into clsatt values ('BUS100','1675812194','200203',5,'2')
insert into clsatt values ('BUS100','1675812194','200203',6,'2')
insert into clsatt values ('BUS100','1675812194','200203',7,'2')
insert into clsatt values ('BUS100','1675812194','200203',8,'2')
insert into clsatt values ('BUS100','1675812194','200203',9,'1')
insert into clsatt values ('BUS100','1675812194','200203',10,'1')
insert into clsatt values ('BUS100','1675812194','200203',11,'1')
insert into clsatt values ('BUS100','1675812194','200203',12,'1')
insert into clsatt values ('BUS100','1712400537','200203',1,'2')
insert into clsatt values ('BUS100','1712400537','200203',2,'2')
insert into clsatt values ('BUS100','1712400537','200203',3,'2')
insert into clsatt values ('BUS100','1712400537','200203',4,'2')
insert into clsatt values ('BUS100','1712400537','200203',5,'2')
insert into clsatt values ('BUS100','1712400537','200203',6,'2')
insert into clsatt values ('BUS100','1712400537','200203',7,'2')
insert into clsatt values ('BUS100','1712400537','200203',8,'1')
insert into clsatt values ('BUS100','1712400537','200203',9,'1')
insert into clsatt values ('BUS100','1712400537','200203',10,'1')
insert into clsatt values ('BUS100','1712400537','200203',11,'1')
insert into clsatt values ('BUS100','1712400537','200203',12,'1')
insert into clsatt values ('BUS100','1801704805','200203',1,'1')
insert into clsatt values ('BUS100','1801704805','200203',2,'1')
insert into clsatt values ('BUS100','1801704805','200203',3,'1')
insert into clsatt values ('BUS100','1801704805','200203',4,'1')
insert into clsatt values ('BUS100','1801704805','200203',5,'1')
insert into clsatt values ('BUS100','1801704805','200203',6,'1')
insert into clsatt values ('BUS100','1801704805','200203',7,'1')
insert into clsatt values ('BUS100','1801704805','200203',8,'1')
insert into clsatt values ('BUS100','1801704805','200203',9,'1')
insert into clsatt values ('BUS100','1801704805','200203',10,'1')
insert into clsatt values ('BUS100','1801704805','200203',11,'1')
insert into clsatt values ('BUS100','1801704805','200203',12,'1')
insert into clsatt values ('BUS100','1922287588','200203',1,'1')
insert into clsatt values ('BUS100','1922287588','200203',2,'1')
insert into clsatt values ('BUS100','1922287588','200203',3,'2')
insert into clsatt values ('BUS100','1922287588','200203',4,'2')
insert into clsatt values ('BUS100','1922287588','200203',5,'2')
insert into clsatt values ('BUS100','1922287588','200203',6,'2')
insert into clsatt values ('BUS100','1922287588','200203',7,'2')
insert into clsatt values ('BUS100','1922287588','200203',8,'2')
insert into clsatt values ('BUS100','1922287588','200203',9,'2')
insert into clsatt values ('BUS100','1922287588','200203',10,'2')
insert into clsatt values ('BUS100','1922287588','200203',11,'1')
insert into clsatt values ('BUS100','1922287588','200203',12,'2')
insert into clsatt values ('BUS100','2188469657','200203',1,'1')
insert into clsatt values ('BUS100','2188469657','200203',2,'1')
insert into clsatt values ('BUS100','2188469657','200203',3,'2')
insert into clsatt values ('BUS100','2188469657','200203',4,'2')
insert into clsatt values ('BUS100','2188469657','200203',5,'2')
insert into clsatt values ('BUS100','2188469657','200203',6,'2')
insert into clsatt values ('BUS100','2188469657','200203',7,'2')
insert into clsatt values ('BUS100','2188469657','200203',8,'2')
insert into clsatt values ('BUS100','2188469657','200203',9,'1')
insert into clsatt values ('BUS100','2188469657','200203',10,'1')
insert into clsatt values ('BUS100','2188469657','200203',11,'1')
insert into clsatt values ('BUS100','2188469657','200203',12,'2')
insert into clsatt values ('BUS100','2515197431','200203',1,'1')
insert into clsatt values ('BUS100','2515197431','200203',2,'1')
insert into clsatt values ('BUS100','2515197431','200203',3,'2')
insert into clsatt values ('BUS100','2515197431','200203',4,'2')
insert into clsatt values ('BUS100','2515197431','200203',5,'1')
insert into clsatt values ('BUS100','2515197431','200203',6,'2')
insert into clsatt values ('BUS100','2515197431','200203',7,'2')
insert into clsatt values ('BUS100','2515197431','200203',8,'1')
insert into clsatt values ('BUS100','2515197431','200203',9,'2')
insert into clsatt values ('BUS100','2515197431','200203',10,'2')
insert into clsatt values ('BUS100','2515197431','200203',11,'1')
insert into clsatt values ('BUS100','2515197431','200203',12,'2')


Celko Answers
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;

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;