Aeries Monthly Summary Report SQL Query

AeriesMonthlyReports.sql

/*
  NAME: AeriesMonthlyAttendanceReport
  AUTH: Jacob Ochoa
  DATE: 2018-11-15 12:02:14.497
  VERSION: 0.2.1

  DESCRIPTION:
   This is a reverse engineered collection of calculated columns often
   used in Aeries Attendance Reports. This Query can be used as a basis
   for creating sql reports that are grouped similarly to some of aeries
   static reporting tools. Hope this helps someone.

   NOTES:
    - ytd.pr needs to be treated as an array not a string. ytd.pr is
      a concatenation of program codes instead of a foreign key lookup.
      Since ytd.pr is a varchar(7) it can store up to 7 seven different
      programs. So the query below brakes them up horizontally as
      separate columns. Its not the greatest solution but what to do,
      the data is already summarized on top of it :(.. Also there
      doesn't seem to be a standard managed place that program code
      description are stored, at least not in my hosted instance of
      aeries, so have fun finding the right join point.

    - ytd is a TABLE not a VIEW, which means to update the YTD table
      you are forced to use their gui. to compute the YTD table use
      this url. make sure to add you domain specific info.
      https://<YOUR AERIES APP DOMAIN>/admin/RunReport.aspx?rptname=PrintMonthlyAttendanceSummary

    - Aeries has no mechanism for preventing 2 people from recomputing
      the ytd table at the same time. so if your try to run reports for
      state reporting I recommend locking out anyone who has the ability
      to run reports to prevent any weird data problems.

    - All numeric values are converted to decimal(38,19) before performing
      math on them. In some cases this query produces slightly different
      number then there pdf counter parts due to rounding. as long as the
      number are with a hundredth of eachother (n.nnxxx), the query should
      be working as intended.

    - This is less of a programming issue as it is a math issue, but
      keep in mind that adding percentages together produces alot of
      rounding issues and inacurate data. Its always better to perform
      a 'group by' operation on the original data set and recalcuate
      the percentages based on the new grouping.

    - I have delcared the queries as sql view creation statements,
      assuming that if you are able to use sql on the aeries database,
      you can probably also create sql views. Other wise you can just
      run the sql states within the create statements.

    - check if your ytd table is the same as mine. run the sql below.
      i got: 0x23C60CCA6F535F6075925BF686E7C6412D809E27

           select
           hashbytes('SHA1',
             cast(BINARY_CHECKSUM(
               CHECKSUM_AGG(BINARY_CHECKSUM(TABLE_NAME)),
               CHECKSUM_AGG(BINARY_CHECKSUM(COLUMN_NAME)),
               CHECKSUM_AGG(BINARY_CHECKSUM(DATA_TYPE))
             ) as varchar(max))
           ) as [HashVersion]
           from INFORMATION_SCHEMA.COLUMNS
             where TABLE_NAME = 'ytd'

*/





drop view if exists MonthlyAttendance
go

create view MonthlyAttendance as (

select

  /* you are most likely to use these for grouping */
  ytd.sc as [SchoolNumber],
  loc.nm as [SchoolName],
  ytd.gr as [Grade],

  /* grade ranges
    double hyphen '--' is used to avoid ambiguity with other data types, like
    excels string->date auto converter.
  */
  case
    when ytd.gr = -2            then cast('PS'    as nvarchar(5))
    when ytd.gr in (-1,0,1,2,3) then cast('TK--3' as nvarchar(5))
    when ytd.gr in (4,5,6)      then cast('4--6'  as nvarchar(5))
    when ytd.gr in (7,8)        then cast('7--8'  as nvarchar(5))
    when ytd.gr in (9,10,11,12) then cast('9--12' as nvarchar(5))
  end as GradeRange,

  ytd.tn as [TeacherNumber],
  tch.te as [TeacherName],
  ytd.mo as [MonthNumber],

  /* programs */
  rtrim(ltrim(concat(
    pr1.de,
    iif(pr2.de is not null, concat(', ', pr2.de),''),
    iif(pr3.de is not null, concat(', ', pr3.de),''),
    iif(pr4.de is not null, concat(', ', pr4.de),''),
    iif(pr5.de is not null, concat(', ', pr5.de),''),
    iif(pr6.de is not null, concat(', ', pr6.de),''),
    iif(pr7.de is not null, concat(', ', pr7.de),'')
  )))                                                 as [Programs],     -- Program descriptions concatenated
  cast(ytd.pr as nvarchar(7))                         as [ProgramCodes], -- original program code concatenation

  /* the individual program code. uncomment if you need them.*/
  --right(left(ytd.pr,1),1)     as [ProgramCode1],
  --right(left(ytd.pr,2),1)     as [ProgramCode2],
  --right(left(ytd.pr,3),1)     as [ProgramCode3],
  --right(left(ytd.pr,4),1)     as [ProgramCode4],
  --right(left(ytd.pr,5),1)     as [ProgramCode5],
  --right(left(ytd.pr,6),1)     as [ProgramCode6],
  --right(left(ytd.pr,7),1)     as [ProgramCode7],

  /* lettered column produced in aeries pdf
    - PDF Column Names are used as a point of reference.
    - some name were modified to remove weird character.
  */
  ytd.da                                as [DaysTaught],               -- MONTHLY ATTENDANCE SUMMARY PDF COL A
  ytd.cf                                as [EnrollmentCarriedForward], -- MONTHLY ATTENDANCE SUMMARY PDF COL B
  ytd.gn                                as [Gains],                    -- MONTHLY ATTENDANCE SUMMARY PDF COL C
  (ytd.cf + ytd.gn)                     as [TotalEnrollment],          -- MONTHLY ATTENDANCE SUMMARY PDF COL D
  ytd.lo                                as [Losses],                   -- MONTHLY ATTENDANCE SUMMARY PDF COL E
  ((ytd.cf + ytd.gn) - ytd.lo)          as [EndingEnrollment],         -- MONTHLY ATTENDANCE SUMMARY PDF COL F
  ytd.ne                                as [DaysNotEnrolled],          -- MONTHLY ATTENDANCE SUMMARY PDF COL G
  ytd.na                                as [DaysNonApportAttend],      -- MONTHLY ATTENDANCE SUMMARY PDF COL H
  ytd.da * (ytd.cf + ytd.gn)            as [ActualDays],               -- MONTHLY ATTENDANCE SUMMARY PDF COL I
  (ytd.da * (ytd.cf + ytd.gn))
  - ytd.ne                              as [MaxDaysPossible],          -- MONTHLY ATTENDANCE SUMMARY TOTALS PDF COL K
  (ytd.da * (ytd.cf + ytd.gn))
  - ytd.ne - ytd.na                     as [TotalApportAttend],        -- MONTHLY ATTENDANCE SUMMARY PDF COL J

  case
    when ((ytd.da * (ytd.cf + ytd.gn))
           - ytd.ne - ytd.na) = 0
      then 0
    else ((ytd.da * (ytd.cf + ytd.gn))
        - ytd.ne - ytd.na) / ytd.da
    end                                 as [TotalADA]                  -- MONTHLY ATTENDANCE SUMMARY PDF COL K


/* YTD
  - select minimum columns needed.
  - adjust/scrub data types.
*/
from (
  select
    sc, --school
    gr, --grade
    tn, --teacher number
    mo, --month
    cast(ltrim(rtrim(pr)) as nchar(7)) as pr,    --programs
    cast(da as decimal(38,19)) as da, --days
    cast(cf as decimal(38,19)) as cf, --carried
    cast(gn as decimal(38,19)) as gn, --gains
    cast(lo as decimal(38,19)) as lo, --losses
    cast(ne as decimal(38,19)) as ne, --not/enr
    cast(na as decimal(38,19)) as na  --nonAppor
  from ytd
  where
    del = 0
) as ytd

/* teacher info */
left join (
  select *
  from tch
  where
    del = 0
) as tch
  on tch.sc = ytd.sc
  and tch.tn = ytd.tn

/* school info */
left join (
  select
    cd,
    ltrim(rtrim(nm)) as nm --nm will be the school name string.
  from loc
  where del = 0
) as loc
  on loc.cd = ytd.sc



/* programs
  this process of breaking up the program codes is repetative
  and compute intensive If anyone has a better idea please let
  me know.
*/
left  join (
    select
      ltrim(rtrim(cd)) as cd,
      max(ltrim(rtrim(de))) as de
    from cod
    where
        tc = 'stu'
        and fc in ('sp','ap1','ap2')
        and del = 0
    group by cd
) as pr1
  on pr1.cd = right(left(ytd.pr,1),1)

left  join (
    select
      ltrim(rtrim(cd)) as cd,
      max(ltrim(rtrim(de))) as de
    from cod
    where
        tc = 'stu'
        and fc in ('sp','ap1','ap2')
        and del = 0
    group by cd
) as pr2
  on pr2.cd = nullif(right(left(ytd.pr,2),1),'')


left  join (
    select
      ltrim(rtrim(cd)) as cd,
      max(ltrim(rtrim(de))) as de
    from cod
    where
        tc = 'stu'
        and fc in ('sp','ap1','ap2')
        and del = 0
    group by cd
) as pr3
  on pr3.cd = nullif(right(left(ytd.pr,3),1),'')


left  join (
    select
      ltrim(rtrim(cd)) as cd,
      max(ltrim(rtrim(de))) as de
    from cod
    where
        tc = 'stu'
        and fc in ('sp','ap1','ap2')
        and del = 0
    group by cd
) as pr4
  on pr4.cd = nullif(right(left(ytd.pr,4),1),'')


left  join (
    select
      ltrim(rtrim(cd)) as cd,
      max(ltrim(rtrim(de))) as de
    from cod
    where
        tc = 'stu'
        and fc in ('sp','ap1','ap2')
        and del = 0
    group by cd
) as pr5
  on pr5.cd = nullif(right(left(ytd.pr,5),1),'')


left  join (
    select
      ltrim(rtrim(cd)) as cd,
      max(ltrim(rtrim(de))) as de
    from cod
    where
        tc = 'stu'
        and fc in ('sp','ap1','ap2')
        and del = 0
    group by cd
) as pr6
  on pr6.cd = nullif(right(left(ytd.pr,6),1),'')

left  join (
    select
      ltrim(rtrim(cd)) as cd,
      max(ltrim(rtrim(de))) as de
    from cod
    where
        tc = 'stu'
        and fc in ('sp','ap1','ap2')
        and del = 0
    group by cd
) as pr7
  on pr7.cd = nullif(right(left(ytd.pr,7),1),'')
)
go


/*
  Aeries Reports
  Monthly Attendance Summary
*/
drop view if exists MonthlyAttendanceSummary
go

create view MonthlyAttendanceSummary as (

select
  MonthNumber,
  Programs,
  SchoolNumber,
  Grade,
  TeacherNumber,

  /* pdf columns */
  max(DaysTaught)               as DaysTaught,               --A
  sum(EnrollmentCarriedForward) as EnrollmentCarriedForward, --B
  sum(Gains)                    as Gains,                    --C
  sum(TotalEnrollment)          as TotalEnrollment,          --D
  sum(Losses)                   as Losses,                   --E
  sum(EndingEnrollment)         as EndingEnrollment,         --F
  sum(DaysNotEnrolled)          as DaysNotEnrolled,          --G
  sum(DaysNonApportAttend)      as DaysNonApportAttend,      --H
  sum(ActualDays)               as ActualDays,               --I
  sum(TotalApportAttend)        as TotalApportAttend,        --j
  sum(TotalADA)                 as TotalADA,                 --K

  case
    when
      sum(TotalApportAttend) = 0
      or sum(MaxDaysPossible) = 0
      then 0
    else
      cast(sum(TotalApportAttend) as decimal(38,19))
      /cast(sum(MaxDaysPossible) as decimal(38,19))
  end                           as PercentAttend,             --L

  /* (k-k*l = m) */
  case
    when
      sum(TotalApportAttend) = 0
      or sum(MaxDaysPossible) = 0
      then 0
    else
      sum(TotalADA) - ( sum(TotalADA)
      * ( cast(sum(TotalApportAttend) as decimal(38,19))
      / cast(sum(MaxDaysPossible) as decimal(38,19)) ))
  end                           as LossAtEndOfLastSchoolDay  --M


from MonthlyAttendance

group by MonthNumber,Programs,SchoolNumber,Grade,TeacherNumber
--order by MonthNumber,Programs,Grade,TeacherNumber
)
go



/*
  Aeries Reports
  Monthly Attendance Summary Totals
*/
drop view if exists MonthlyAttendanceSummaryTotals
go

create view MonthlyAttendanceSummaryTotals as (
select
  MonthNumber,
  Programs,
  SchoolNumber,
  GradeRange,

  /* pdf columns */
  max(DaysTaught)               as DaysTaught,               --A
  sum(EnrollmentCarriedForward) as EnrollmentCarriedForward, --B
  sum(Gains)                    as Gains,                    --C
  sum(TotalEnrollment)          as TotalEnrollment,          --D
  sum(Losses)                   as Losses,                   --E
  sum(EndingEnrollment)         as EndingEnrollment,         --F
  sum(DaysNotEnrolled)          as DaysNotEnrolled,          --G
  sum(DaysNonApportAttend)      as DaysNonApportAttend,      --H
  sum(TotalApportAttend)        as TotalApportAttend,        --I
  sum(TotalADA)                 as TotalADA,                 --J
  sum(MaxDaysPossible)          as MaxDaysPossible,          --K

  case
    when
      sum(TotalApportAttend) = 0
      or sum(MaxDaysPossible) = 0
      then 0
    else
      cast(sum(TotalApportAttend) as decimal(38,19))
      /cast(sum(MaxDaysPossible) as decimal(38,19))
  end                           as PercentAttend             --L

from MonthlyAttendance

group by MonthNumber,Programs,SchoolNumber,GradeRange
--order by MonthNumber,Programs,GradeRange
)
go

perfect for auditors

select 

    -- Group By
    MonthNumber,
    SchoolName,
    Programs   as [AttendancePrograms],
    Grade      as [StudentGrade],
    DaysTaught as [SchoolDaysInMonth],
    
    -- enrollment change log durring month
    SUM(EnrollmentCarriedForward) as [EnrollmentAtBeginingOfMonth],
    SUM(Gains)                    as [StudentsGainedInMonth],
    SUM(Losses)                   as [StudentsLossedInMonth],
    SUM(TotalEnrollment)          as [EnrollmentPeakInMonth],
    SUM(EndingEnrollment)         as [EnrollmentAtEndOfMonth],
    
    -- enrollment and attendance converted to days
    SUM(MaxDaysPossible)     as [TotalDaysEnrolled],
    SUM(DaysNonApportAttend) as [TotalDaysAbsent],
    SUM(TotalApportAttend)   as [TotalDaysAttended],

    -- Percentages
    CASE
        WHEN SUM(MaxDaysPossible) = 0 then 0
        ELSE SUM(MaxDaysPossible)/DaysTaught
    END AS [AverageDailyEnrollment],

    CASE
        WHEN SUM(DaysNonApportAttend) = 0 then 0
        ELSE SUM(DaysNonApportAttend)/DaysTaught
    END AS [AverageDailyAbsences],

    CASE
        WHEN SUM(TotalApportAttend) = 0 then 0
        ELSE SUM(TotalApportAttend)/DaysTaught
    END AS [AverageDailyAttendance]

from MonthlyAttendance
group by 
    MonthNumber,
    SchoolName,
    Programs,
    Grade,
    DaysTaught