PRIDE Report

Once grades are stored for a progress report or grade card our high school uses data such as number of referrals, attendance percentage, and grade counts to determine their CAT Time/reward level for the next few weeks.  The report is written in sqlReports 4 and the result looks like this:

pride-report-screenshot

In order to run the report you must enter the date range for attendance and discipline referral counting and the store code for grades:

screen-shot-2017-01-16-at-9-09-14-am-2

And the SQL code, note things like the log.logtypeid and adjust as needed.  Also you can see where ‘Neosho High School’ is hard-coded as the school name – this can be adjusted or removed depending on how you want to handle students who transfer in.  As it is, the report will only count stored grades from Neosho High School.  You could add this as a parameter on the report but to keep things simple for our end users I just put it right in the SQL.

The report is also available on Github

WITH tot AS (select 
  pmd.studentid, 
  pmd.schoolid, 
  pmd.GRADE_LEVEL, 
  round(sum(
    CASE
    when pad.total_minutes is null then pmd.potential_minutes_present
    else pad.total_minutes
    end)/60,2) act_min,
  round(sum(pmd.potential_minutes_present)/60,2) pot_min, 
  round(sum(pmd.potential_minutes_present)/60 - sum(
    CASE
    when pad.total_minutes is null then pmd.potential_minutes_present
    else pad.total_minutes
    end)/60,2) min_diff,
  count(*) memb
from ps_membership_defaults pmd
left outer join PS_ATTENDANCE_DAILY pad
on pmd.studentid=pad.studentid
and pmd.calendardate=pad.att_Date
where pmd.calendardate >= '%param1%' and pmd.calendardate <= '%param2%'
group by pmd.studentid, pmd.schoolid, pmd.GRADE_LEVEL),

sch_memb as (
  select 
    schoolid,
    max(tot.memb) max_memb
  from tot
  group by schoolid
),

stu_odr as (
  select
   students.id st_id,
   count(*) as tot
  from
   students
  join
   log
  on
   students.id = log.studentid
  where
   log.logtypeid = -100000
  and
   log.entry_date between '%param1%' and '%param2%'
  and
   students.schoolid = ~(curschoolid)
  group by
   students.id
),

stu_grades as (
  select
   studentid,
   SUM(case when grade like 'A%' then 1 else 0 end) Agrade,
   SUM(case when grade like 'B%' then 1 else 0 end) Bgrade,
   SUM(case when grade like 'C%' then 1 else 0 end) Cgrade,
   SUM(case when grade like 'D%' then 1 else 0 end) Dgrade,
   SUM(case when grade like 'F%' then 1 else 0 end) Fgrade
  from
   storedgrades
  where
   storecode = '%param3%' 
  and 
   datestored > '%param1%'
  and
   schoolname = 'Neosho High School'
  group by
   studentid
),

TotalPossible as (
    Select studentid, 
    sum(attendancevalue) as present,
    sum(membershipvalue)-sum(attendancevalue) as absent,
    sum(membershipvalue) as TotalPossible

    From PS_AdaAdm_Defaults_All 

    Where attendancevalue is not NULL and calendardate>='%param1%' and calendardate<='%param2%'
    Group by studentid
)


SELECT students.student_number, students.lastfirst, students.grade_level, '%param3%', stu_grades.Agrade, stu_grades.Bgrade, stu_grades.Cgrade, stu_grades.Dgrade, stu_grades.Fgrade, round((tot.act_min / tot.pot_min) * 100,2), nvl(to_number(stu_odr.tot), 0), 
(case when to_number(stu_grades.Fgrade, '99') = 0 and to_number(stu_grades.Dgrade, '99') = 0 and to_number(stu_grades.Cgrade, '99') = 0 and round((tot.act_min / tot.pot_min) * 100,2) >= %param4% and NVL(to_number(stu_odr.tot), 0) = 0 then 'Gold'
          when to_number(stu_odr.tot, '99') >= 1 then 'Bronze' 
          when to_number(stu_grades.Fgrade, '99') = 0 and to_number(stu_grades.Dgrade, '99') = 0 and round((tot.act_min / tot.pot_min) * 100,2) >= %param5% and NVL(to_number(stu_odr.tot), 0) = 0 then 'Silver'
          else 'Bronze' end), TotalPossible.absent
FROM students 
JOIN tot
ON students.id = tot.studentid
LEFT OUTER JOIN stu_grades
ON stu_grades.studentid = students.id
LEFT OUTER JOIN stu_odr
ON stu_odr.st_id = stu_grades.studentid
LEFT JOIN TotalPossible
ON TotalPossible.studentid = students.id
WHERE students.schoolid = ~(curschoolid)
AND tot.pot_min > 0
AND students.enroll_status = 0

 

 

Advertisements
PRIDE Report

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s