asked on

Oracle count absences for student by date and then sum to get total days missed.

I have a query that counts courses missed by a student on a daily basis. This is secondary students that can miss a maxium of 4 classes
per day. So based on the count if they miss 4 or 5 classes on the same day it is considered missing 1 day of school. If they miss 2 or 3 classes it is one half day missed. We ignore 1 class. I'm not sure how to get total based on those criteria.
For example, below data should have student missing 3 1/2 days.

Student classes missed on this day
0001100111      5      14-10-31
0001100111      4      15-03-03
0001100111      1      15-02-11
0001100111      4      15-04-02
0001100011      3      15-01-12

Here is base query.

select
t1.person_id as student,
      count( t1.calendar_date) as tot_days_absent,
t1.calendar_date as day_absent
FROM period_attendance t1
/* To get school BSID */
INNER JOIN schools t2 ON
      t1.school_code = t2.school_code
/* To get the grade */
INNER JOIN student_registrations t5 ON
      t1.school_code = t5.school_code AND
      t1.school_year = t5.school_year AND
     t1.person_id = t5.person_id AND
t5.status_indicator_code = 'Active'
/* To get course and program */
LEFT OUTER JOIN student_program_class_tracks t6 ON
      t1.school_code = t6.school_code AND
      t1.school_year = t6.school_year AND
      t1.school_year_track = t6.school_year_track AND
t1.person_id = t6.person_id AND
      t1.class_code = t6.class_code AND
      /* logic to include both secondary (first part of OR statement) and
      elementary (second part of OR statement) records */
      (
      (t6.builder_set_code = 'working') OR
      (t6.builder_set_code is null AND t6.course_code is null)
      ) AND
      /* This is needed to deal with situations when there can be more than 1 course
            for the same student/class - e.g. Student changed from Academic
            to College Course */
      (t1.calendar_date BETWEEN t6.start_date AND
      t6.end_date)
WHERE t1.class_code is not null
and t1.person_id = 'xxxxxxxxx'
and t1.attendance_code = 'A'
and t1.school_period <> 'HR'
and t6.school_year = '20142015'
and t5.school_year = '20142015'
and t1.school_year = '20142015'
group by t1.person_id,t1.calendar_date
order by t1.person_id;

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

you probably want to remove the order by on inner query

and put one on the outer query
pulling it all together for something like this...

SELECT student, SUM(CASE WHEN tot_days_absent <= 3 THEN 0.5 WHEN tot_days_absent >= 4 THEN 1 END)
     FROM  
   (


 select
 t1.person_id as student,
       count( t1.calendar_date) as tot_days_absent,
   t1.calendar_date as day_absent
 FROM period_attendance t1 
 /* To get school BSID */
 INNER JOIN schools t2 ON
       t1.school_code = t2.school_code
 /* To get the grade */
 INNER JOIN student_registrations t5 ON
       t1.school_code = t5.school_code AND
       t1.school_year = t5.school_year AND 
        t1.person_id = t5.person_id AND
   t5.status_indicator_code = 'Active'
 /* To get course and program */
 LEFT OUTER JOIN student_program_class_tracks t6 ON
       t1.school_code = t6.school_code AND
       t1.school_year = t6.school_year AND
       t1.school_year_track = t6.school_year_track AND
   t1.person_id = t6.person_id AND
       t1.class_code = t6.class_code AND
       /* logic to include both secondary (first part of OR statement) and
       elementary (second part of OR statement) records */
       (
       (t6.builder_set_code = 'working') OR
       (t6.builder_set_code is null AND t6.course_code is null)
       ) AND
       /*  This is needed to deal with situations when there can be more than 1 course
             for the same student/class - e.g. Student changed from Academic
             to College Course */
       (t1.calendar_date BETWEEN t6.start_date AND
       t6.end_date)
 WHERE t1.class_code is not null
       and t1.person_id = 'xxxxxxxxx'
       and t1.attendance_code = 'A'
       and t1.school_period <> 'HR'
       and t6.school_year = '20142015'
       and t5.school_year = '20142015'
       and t1.school_year = '20142015'
 group by t1.person_id,t1.calendar_date

 )
    WHERE tot_days_absent > 1
 GROUP BY student
order by student;

Open in new window

guinnie

ASKER

Thank you SDSTUBER