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:

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

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