We help IT Professionals succeed at work.
Get Started

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

guinnie
guinnie asked
on
344 Views
Last Modified: 2015-04-09
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;
Comment
Watch Question
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE