• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

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;
0
guinnie
Asked:
guinnie
  • 2
1 Solution
 
sdstuberCommented:
SELECT student, SUM(CASE WHEN tot_days_absent <= 3 THEN 0.5 WHEN tot_days_absent >= 4 THEN 1 END)
    FROM  
  (
     ... your query above ...
)
   WHERE tot_days_absent > 1
GROUP BY student;



also, I made the assumption there is a typo in the student id of your last line of sample results.

I assume it should be the same as the previous lines
0
 
sdstuberCommented:
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

0
 
guinnieAuthor Commented:
Thank you SDSTUBER
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now