|
|
Posted by rcamarda on 05/04/06 17:04
Hello,
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')
Navigation:
[Reply to this message]
|