Hi All-
I need to create a report that shows number of occurrences of a student being suspended (by 2 different suspension codes) for a range of dates (usually first day of month and last day of month) by school.
Here is the starting query, that I use to come up with the data set.
SELECT s.student_number,
s.grade_level,
att.schoolid "SCH",
att.att_date,
to_char(att.att_date, 'DAY') day ,
ac.att_code
FROM students s, attendance att, attendance_code ac
WHERE s.id = att.studentid AND
att.ATTENDANCE_CODEID = ac.ID AND
ac.att_code in ('S','X') AND
att.ATT_MODE_CODE = 'ATT_ModeDaily' and
att.SCHOOLID in (82) and
att.ATT_DATE between TO_DATE('11/01/2016','MM/DD/YYYY') and TO_DATE('11/30/2016','MM/DD/YYYY')
order by 6,1,4
The part of output is like so:
181535 10 82 11/03/2016 S 1
181535 10 82 11/04/2016 S
181535 10 82 11/07/2016 S
181535 10 82 11/16/2016 S 1
181535 10 82 11/17/2016 S
181535 10 82 11/18/2016 S
The last column are the number of occurrences. Consecutive dates are to be considered as one occurrence, irrespective of a week end
and or a school holiday. I do have the school calendar in a separate table.
What would be the best way that this can be produced?
Thanks!
=============
In continuation, here are the create statements, sample data and expected results. thank!
CREATE TABLE STUDENTS
(
ID NUMBER(10) NOT NULL,
schoolid number(10),
STUDENT_NUMBER NUMBER(10)
)
/
insert into students values(1821,82,181535) ;
insert into students values(13892,82,180784);
insert into students values(12877,82,161314);
insert into students values(14088,82,181728);
/
CREATE TABLE ATTENDANCE
(
ID NUMBER(10) NOT NULL,
CALENDAR_DAYID NUMBER(10) ,
ATTENDANCE_CODEID NUMBER(10),
STUDENTID NUMBER(10),
schoolid number(10),
ATT_MODE_CODE VARCHAR2(20 CHAR),
ATT_DATE DATE
)
/
insert into attendance values ( 23642330,254090,10126,1821,82,'ATT_ModeDaily',03-NOV-16);
insert into attendance values ( 23642335,254091,10126,1821,82,'ATT_ModeDaily',04-NOV-16);
insert into attendance values ( 23676557,254094,10126,1821,82,'ATT_ModeDaily',07-NOV-16);
insert into attendance values ( 23726471,254103,10126,1821,82,'ATT_ModeDaily',16-NOV-16);
insert into attendance values ( 23726457,254104,10126,1821,82,'ATT_ModeDaily',17-NOV-16);
insert into attendance values ( 23726461,254105,10126,1821,82,'ATT_ModeDaily',18-NOV-16);
insert into attendance values ( 23707597,254102,10126,13892,82,'ATT_ModeDaily',15-NOV-16);
insert into attendance values ( 23710920,254103,10126,13892,82,'ATT_ModeDaily',16-NOV-16);
insert into attendance values ( 23726967,254104,10126,13892,82,'ATT_ModeDaily',17-NOV-16);
insert into attendance values ( 23726965,254105,10126,13892,82,'ATT_ModeDaily',18-NOV-16);
insert into attendance values ( 23766755,254108,10126,13892,82,'ATT_ModeDaily',21-NOV-16);
insert into attendance values ( 23766780,254109,10126,13892,82,'ATT_ModeDaily',22-NOV-16);
insert into attendance values ( 23766753,254110,10126,13892,82,'ATT_ModeDaily',23-NOV-16);
insert into attendance values ( 23810950,254115,10126,13892,82,'ATT_ModeDaily',28-NOV-16);
insert into attendance values ( 23829495,254116,10126,13892,82,'ATT_ModeDaily',29-NOV-16);
insert into attendance values ( 23759842,254105,10130,12877,82,'ATT_ModeDaily',18-NOV-16);
insert into attendance values ( 23818901,254115,10130,12877,82,'ATT_ModeDaily',28-NOV-16);
insert into attendance values ( 23670409,254094,10130,14088,82,'ATT_ModeDaily',07-NOV-16);
insert into attendance values ( 23693514,254096,10130,14088,82,'ATT_ModeDaily',09-NOV-16);
/
CREATE TABLE ATTENDANCE_CODE
( ID NUMBER(10) NOT NULL,
SCHOOLID NUMBER(10),
ATT_CODE VARCHAR2(10 CHAR)
)
/
INSERT INTO ATTENDANCE_CODE VALUES (10126,82,'S');
INSERT INTO ATTENDANCE_CODE VALUES (10130,82,'X');
/
--- CALENDAR TABLE LOOK UP --- CODE 1 IS WORKING 0 IS NONWORKING
CREATE TABLE CALENDAR_DAY
( ID NUMBER(10) NOT NULL,
SCHOOLID NUMBER(10),
DATE_VALUE DATE,
INSESSION NUMBER(10)
)
/
254088,82,01-NOV-16,1;
254089,82,02-NOV-16,1;
254090,82,03-NOV-16,1;
254091,82,04-NOV-16,1;
254092,82,05-NOV-16,0;
254093,82,06-NOV-16,0;
254094,82,07-NOV-16,1;
254095,82,08-NOV-16,0;
254096,82,09-NOV-16,1;
254097,82,10-NOV-16,0;
254098,82,11-NOV-16,0;
254099,82,12-NOV-16,0;
254100,82,13-NOV-16,0;
254101,82,14-NOV-16,1;
254102,82,15-NOV-16,1;
254103,82,16-NOV-16,1;
254104,82,17-NOV-16,1;
254105,82,18-NOV-16,1;
254106,82,19-NOV-16,0;
254107,82,20-NOV-16,0;
254108,82,21-NOV-16,1;
254109,82,22-NOV-16,1;
254110,82,23-NOV-16,1;
254111,82,24-NOV-16,0;
254112,82,25-NOV-16,0;
254113,82,26-NOV-16,0;
254114,82,27-NOV-16,0;
254115,82,28-NOV-16,1;
254116,82,29-NOV-16,1;
254117,82,30-NOV-16,1;
/
STU # GRADE SCH # DATE CODE OCCURRENCE
180784 10 82 11/15/2016 S 1
180784 10 82 11/16/2016 S
180784 10 82 11/17/2016 S
180784 10 82 11/18/2016 S
180784 10 82 11/21/2016 S
180784 10 82 11/22/2016 S
180784 10 82 11/23/2016 S
180784 10 82 11/28/2016 S
180784 10 82 11/29/2016 S
181535 10 82 11/03/2016 S 1
181535 10 82 11/04/2016 S
181535 10 82 11/07/2016 S
181535 10 82 11/16/2016 S 1
181535 10 82 11/17/2016 S
181535 10 82 11/18/2016 S
161314 9 82 11/18/2016 X 1
161314 9 82 11/28/2016 X 1
181728 9 82 11/07/2016 X 1
181728 9 82 11/09/2016 X
Thanks for providing the test scripts... even if they need a little massaging ;)
My understanding of what you want is:
- Display a 1 on the first date in a run of consecutive attendances
- Consecutive dates exclude days the school was closed. So if someone attended on Friday and the following Monday, this is consecutive (because Weekend!)
If so, you can do it like so:
- Join the student and attendance tables together. For each student also return the previous attendance date. Use lag() to do this
- Separately return all the days school is open. Again, for each of these days, calculate the previous day it was open
You now have two tables of data. If, for a particular attendance, the previous attendance <> the previous day school is open this is a new sequence. So to get the output you've requested:
- Join the attendance data to the filtered calendar data on date
- Use case to see if the previous dates from the two data sets match
This gives you:
CREATE TABLE STUDENTS (
ID NUMBER(10) NOT NULL,
schoolid number(10),
STUDENT_NUMBER NUMBER(10)
)
/
insert into students values(1821,82,181535) ;
insert into students values(13892,82,180784);
insert into students values(12877,82,161314);
insert into students values(14088,82,181728);
/
CREATE TABLE ATTENDANCE (
ID NUMBER(10) NOT NULL,
CALENDAR_DAYID NUMBER(10) ,
ATTENDANCE_CODEID NUMBER(10),
STUDENTID NUMBER(10),
schoolid number(10),
ATT_MODE_CODE VARCHAR2(20 CHAR),
ATT_DATE DATE
)
/
insert into attendance values (
23642330,254090,10126,1821,82,'ATT_ModeDaily',to_date('03-NOV-16', 'dd-MON-YY'));
insert into attendance values (
23642335,254091,10126,1821,82,'ATT_ModeDaily',to_date('04-NOV-16', 'dd-MON-YY'));
insert into attendance values (
23676557,254094,10126,1821,82,'ATT_ModeDaily',to_date('07-NOV-16', 'dd-MON-YY'));
insert into attendance values (
23726471,254103,10126,1821,82,'ATT_ModeDaily',to_date('16-NOV-16', 'dd-MON-YY'));
insert into attendance values (
23726457,254104,10126,1821,82,'ATT_ModeDaily',to_date('17-NOV-16', 'dd-MON-YY'));
insert into attendance values (
23726461,254105,10126,1821,82,'ATT_ModeDaily',to_date('18-NOV-16', 'dd-MON-YY'));
insert into attendance values (
23707597,254102,10126,13892,82,'ATT_ModeDaily',to_date('15-NOV-16', 'dd-MON-YY'));
insert into attendance values (
23710920,254103,10126,13892,82,'ATT_ModeDaily',to_date('16-NOV-16', 'dd-MON-YY'));
insert into attendance values (
23726967,254104,10126,13892,82,'ATT_ModeDaily',to_date('17-NOV-16', 'dd-MON-YY'));
insert into attendance values (
23726965,254105,10126,13892,82,'ATT_ModeDaily',to_date('18-NOV-16', 'dd-MON-YY'));
insert into attendance values (
23766755,254108,10126,13892,82,'ATT_ModeDaily',to_date('21-NOV-16', 'dd-MON-YY'));
insert into attendance values (
23766780,254109,10126,13892,82,'ATT_ModeDaily',to_date('22-NOV-16', 'dd-MON-YY'));
insert into attendance values (
23766753,254110,10126,13892,82,'ATT_ModeDaily',to_date('23-NOV-16', 'dd-MON-YY'));
insert into attendance values (
23810950,254115,10126,13892,82,'ATT_ModeDaily',to_date('28-NOV-16', 'dd-MON-YY'));
insert into attendance values (
23829495,254116,10126,13892,82,'ATT_ModeDaily',to_date('29-NOV-16', 'dd-MON-YY'));
insert into attendance values (
23759842,254105,10130,12877,82,'ATT_ModeDaily',to_date('18-NOV-16', 'dd-MON-YY'));
insert into attendance values (
23818901,254115,10130,12877,82,'ATT_ModeDaily',to_date('28-NOV-16', 'dd-MON-YY'));
insert into attendance values (
23670409,254094,10130,14088,82,'ATT_ModeDaily',to_date('07-NOV-16', 'dd-MON-YY'));
insert into attendance values (
23693514,254096,10130,14088,82,'ATT_ModeDaily',to_date('09-NOV-16', 'dd-MON-YY'));
CREATE TABLE ATTENDANCE_CODE
( ID NUMBER(10) NOT NULL,
SCHOOLID NUMBER(10),
ATT_CODE VARCHAR2(10 CHAR)
)
/
INSERT INTO ATTENDANCE_CODE VALUES (10126,82,'S');
INSERT INTO ATTENDANCE_CODE VALUES (10130,82,'X');
/
CREATE TABLE CALENDAR_DAY
( ID NUMBER(10) NOT NULL,
SCHOOLID NUMBER(10),
DATE_VALUE DATE,
INSESSION NUMBER(10)
)
/
insert into calendar_day values (254088,82,to_date('01-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254089,82,to_date('02-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254090,82,to_date('03-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254091,82,to_date('04-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254092,82,to_date('05-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254093,82,to_date('06-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254094,82,to_date('07-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254095,82,to_date('08-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254096,82,to_date('09-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254097,82,to_date('10-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254098,82,to_date('11-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254099,82,to_date('12-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254100,82,to_date('13-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254101,82,to_date('14-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254102,82,to_date('15-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254103,82,to_date('16-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254104,82,to_date('17-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254105,82,to_date('18-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254106,82,to_date('19-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254107,82,to_date('20-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254108,82,to_date('21-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254109,82,to_date('22-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254110,82,to_date('23-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254111,82,to_date('24-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254112,82,to_date('25-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254113,82,to_date('26-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254114,82,to_date('27-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254115,82,to_date('28-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254116,82,to_date('29-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254117,82,to_date('30-NOV-16', 'dd-MON-YY'),1);
commit;
with att as (
select s.student_number, a.schoolid, a.att_date, ac.att_code,
lag(att_date, 1, date'1900-01-01') over (partition by s.student_number order by att_date) prev
from attendance a
join students s
on a.studentid = s.id
join attendance_code ac
on a.attendance_codeid = ac.id
and a.schoolid = ac.schoolid
), dates as (
select date_value, lag(date_value, 1, date'1900-01-01') over (order by date_value) prev_dt
from calendar_day
where insession = 1
)
select att.student_number, att.schoolid, att.att_date, att.att_code,
case when prev != prev_dt then 1 end occ
from dates
join att
on att_date = date_value
order by student_number, att_date;
STUDENT_NUMBER SCHOOLID ATT_DATE ATT_CODE OCC
161,314 82 18-NOV-2016 X 1
161,314 82 28-NOV-2016 X 1
180,784 82 15-NOV-2016 S 1
180,784 82 16-NOV-2016 S
180,784 82 17-NOV-2016 S
180,784 82 18-NOV-2016 S
180,784 82 21-NOV-2016 S
180,784 82 22-NOV-2016 S
180,784 82 23-NOV-2016 S
180,784 82 28-NOV-2016 S
180,784 82 29-NOV-2016 S
181,535 82 03-NOV-2016 S 1
181,535 82 04-NOV-2016 S
181,535 82 07-NOV-2016 S
181,535 82 16-NOV-2016 S 1
181,535 82 17-NOV-2016 S
181,535 82 18-NOV-2016 S
181,728 82 07-NOV-2016 X 1
181,728 82 09-NOV-2016 X