Absence Events Daily Calculations

Please read all of the following to fully understand how to effectively utilise the absence events count. To correctly calculate the student absences, you must use Timetable Maintenance, at a minimum, the following areas should be populated:

• Timetable Definition 
• Calendar Events – At a minimum, one record to determine which day (D1), Day 1 your timetable starts 
• Timetable Group 
• YearLevels (luYearLevel) > TimetableGroup 
• Check field in Timetable Maintenance > Timetable Definition tab, called OverrideAttendanceMinutes, which will override the length of a period for slips (explained in more detail below).

There are many ways that schools break their reporting periods in to. Some call it Term. Half Term, others call it Semester or Report Number. This number is stored in the particular tables as the column name FileSemester. To avoid confusion throughout the rest of this documentation we will refer to this as Semester.

You must use File Semester Maintenance and populate the File Semesters with the correct Start and End Dates. If in the case your semesters overlap, the minimum date will be used, and therefore the corresponding Semester for that date.

The following table, dbo.AbsenceEventsDailyCalc is used to populate daily Absence Data.

This has the fields:

• AbsenceEventsDailyCalcSeq - This is to give each record a unique identifier. 
• AbsenceDate – The day of the absence 
• ID– The Students ID in Synergetic 
• AbsenceFactor – Factor (or percentage) of the day that the student is away from school. E.g. 1.00 = 100%, 0.36 = 36% This figure is based on the total number of minutes the student is absent from school divided by the total number of schooling minutes in the day. This field is rounded to 2 decimal places. 
• AbsenceMinutes – The total number of minutes the student is absent from school. 
• CreatedDate – This is the date that this record was first calculated. 
• ModifiedDate – This is the date that this record was last re-evaluated. 
• SlipsUnexplainedCount – These are the total number of unexplained slips. If a slip is unexplained and cannot be traced to an actual event, the slip will be matched to Timetable Maintenance > Timetable Definition tab. If the field OverrideAttendanceMinutes has a value, this amount of time will be added to the AbsenceMinutes, if it is not populated, the length of the period that the Absence Slip falls into will be used. 
• DayStartTime – This is to determine the schooling start time. 
• DayEndTime – This is to determine the schooling end time. The DayStartTime and DayEndTime are derived from Timetable Maintenance > Definition Tab. If either of these fields are NULL, this means that the absence date fall outside the range of dates in File Semester Maintenance. The DayStartTime and DayEndTime are important fields as they determine the time to calculate when school starts and school ends. Any Absence Events outside of this time are not used in the calculations.
• TimetableGroup – The students timetable group. This is derived from the students year and campus and matched to luYearLevel. 
• FileYear – The Year of which the absence event is recorded against. 
• FileSemester – The Semester of which the absence event is recorded against. 
• DayNumber – Based on Timetable Maintenance > Definition tab and Calendar Events tab. Determines which day the AbsenceDate falls on.

The stored procedure to calculate these figures is called: dbo.spgAbsenceEventsDailyCalc. This procedure will be set up on your school to run once every day. This will recalculate any Absence Events that have been created, modified or deleted. You can manually run this yourself by executing the following command in the relevant database:

EXEC dbo.spgAbsenceEventsDailyCalc

**Please only execute this code if you have the expertise.

The following configuration keys in Configuration File Maintenance have been added:

These keys have been used in the stored procedure crspAbsenceEventsDailyCalc.

The following stored procedure, crspAbsenceEventsDailyCalc, has been included for use in Crystal Reports. It can accept the following variables:

The procedure contains the following fields from the students Community table: 

As your site may wish to allow for a few absent minutes throughout the day and not count this towards the total absent days, you may wish to count to the nearest none (0.0), half (0.5) or whole day (1.0) absence, this can be calculated using the above configuration keys.

For example, you may have the ContactMinutesUpper as 240 and the ContactMinutesLower as 30.

E.g. Student: Aaron Smith, assuming a 360 minute school day

• Day 1 - they are absent for a total of 29 minutes 

  Field	Value
  AbsenceFactorRawTotal	0.08
  AbsenceFactorRoundedTotal	0.0

• Day 2 – they are absent for a total of 130 minutes

  Field	Value
  AbsenceFactorRawTotal	0.36
  AbsenceFactorRoundedTotal	0.5

• Day 3 – they are absent for a total of 245 minutes 

  Field	Value
  AbsenceFactorRawTotal	0.68
  AbsenceFactorRoundedTotal	1.0

• So the total displayed from crspAbsenceEventsDailyCalc would be:

  Field	Value
  AbsenceFactorRawTotal	1.12
  AbsenceFactorRoundedTotal	1

The rules are:

• If the  AbsenceMinutes is less than the ContactMinutesLower then count as 0.0
• If the ContactMinutesLower is equal to or greater than the AbsenceMinutes, and the ContactMinutesUpper is less than or equal to the AbsenceMinutes, then count as 0.5
• If the ContactMinutesUpper is greater than the AbsenceMinutes, then count as 1.0

A view has been created to easily identify the Absence Events that have and have not been included in the calculations, this is called vAbsenceEventsDailyCalc.

Space to write up Documentation for Daily Calc.

To give an example of the uses of these tables, views and stored procedures, the following reports have been included under the Attendance Module:

STUABSEVTCOUNT - Student Absence Events Count Summary

This report uses the base table AbsenceEventsDailyCalc joined to the view vStudents. As this is not utilising crspAbsenceEventsDailyCalc, there is no rounded total count. This has been worked around by creating a formula within the Crystal Report called RoundedAbsenceCount which utilises the following AbsenceFactor values to calculate the respective no day / half day / full day absence values:

IF {AbsenceEventsDailyCalc.AbsenceFactor} < .1     THEN 0.0 ELSE IF {AbsenceEventsDailyCalc.AbsenceFactor} >= .1 AND {AbsenceEventsDailyCalc.AbsenceFactor} <= .9     THEN 0.5 ELSE IF {AbsenceEventsDailyCalc.AbsenceFactor} > .9     THEN 1.0 

STUABSEVTCOUNTD - Student Absence Events Count Detailed

The main report uses a view, vAbsenceEventsDailyCalc. The stored procedure crspAbsenceEventsDailyCalc is included in a sub-report. The ID, AbsenceDateFrom and AbsenceDateTo parameter fields are passed through via the sub-report links. This report displays all the Absence Events that spgAbsenceEventsDailyCalc is using to calculate its figures; you can use this report to determine that the counts being returned are accurate. This report will be a useful example for schools that wish to display on student assessment reports the number of days the student has been absent.

NOTES Slips - if the Period selected is: AM or PM then 0.5 day is added to the table AbsenceEventsDailyCalc, AbsenceFactor field ALL DAY then 1.0 day is added to the table AbsenceEventsDailyCalc, AbsenceFactor field If DayStartTime and DayEndTime are NULL, i.e. File Semester or Timetable Definition has not been set correctly the only absences calculated are AllDay or Slips with AllDay. These do not have to fall inside the Start and End times.