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;
guinnieAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.