stats.sql

WITH included_students AS
    (
    SELECT
        student.student_id,
        TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(form.short_name),'yr',''),'year',''),'y',''),'ib',''),'oc','')) AS YEAR_LEVEL,
        CASE WHEN contact.gender_id = 2 THEN 1 ELSE 0 END AS MALE,
        CASE WHEN contact.gender_id = 3 THEN 1 ELSE 0 END AS FEMALE,
        CASE WHEN contact.gender_id = 2 AND student.indigenous_id IN (2,3,4) THEN 1 ELSE 0 END IND_MALE,
        CASE WHEN contact.gender_id = 3 AND student.indigenous_id IN (2,3,4) THEN 1 ELSE 0 END IND_FEMALE,
        student_form_run.start_date,
        student_form_run.end_date,
        form_run.TIMETABLE_ID
    FROM student_form_run
        INNER JOIN form_run ON form_run.form_run_id = student_form_run.form_run_id
        INNER JOIN form ON form.form_id = form_run.form_id
            AND (RIGHT(form.form,2) IN (' 1','01',' 2','02','10',' 3',' 4',' 5',' 6',' 7',' 8',' 9','03','04','05','06','07','08','09'))
        -- student details
        INNER JOIN student ON student.student_id = student_form_run.student_id
        INNER JOIN contact ON contact.contact_id = student.contact_id
    WHERE student_form_run.end_date >= DATE('[[Enter From Date=date]]') 
        AND student_form_run.start_date <= DATE('[[Enter To Date=date]]')
    )
    ,
    enrolments_absences_each_day as
    (
    	select
    	da.student_id,
    	ins.year_level,
        case when term.term like '%1%' or term.term like '%2%' then 'Semester 1' when term.term like '%3%' then 'Term 3' when term.term like '%4%' then 'Term 4' else term.term end as terms,
        sum(1-
                CASE WHEN am_attendance_status_id BETWEEN 2 AND 7 OR ((am_attendance_status_id is null OR am_attendance_status_id = 0) AND pm_attendance_status_id BETWEEN 2 AND 7) THEN 0.5
                    WHEN am_attendance_status_id BETWEEN 8 AND 19 THEN 0.25
                    ELSE 0 END -
                CASE WHEN pm_attendance_status_id BETWEEN 2 AND 7 OR ((pm_attendance_status_id is null OR pm_attendance_status_id = 0) AND am_attendance_status_id BETWEEN 2 AND 7) THEN 0.5
                    WHEN pm_attendance_status_id BETWEEN 8 AND 19 THEN 0.25
                    ELSE 0 END) AS TOTAL_ATTENDANCE_DAYS,
                count(da.student_id) as TOTAL_ENROLMENT_DAYS,
                
                male,
                female,
                ind_male,
                ind_female   
        from included_students ins 
        left join daily_attendance da on ins.student_id = da.student_id and date_on between date('[[Enter From Date=date]]') and date('[[Enter To Date=date]]')
        left join TERM on da.date_on between term.START_DATE and term.END_DATE and ins.timetable_id = term.timetable_id
        group by da.student_id, ins.year_level, case when term.term like '%1%' or term.term like '%2%' then 'Semester 1' when term.term like '%3%' then 'Term 3' when term.term like '%4%' then 'Term 4' else term.term end, male, female, ind_male, ind_female
    ),
    add_per as
    (
    select eaed.*, (eaed.total_attendance_days/total_enrolment_days)*100 as attendance_percentage from enrolments_absences_each_day eaed
    ),
    result1 as(
    select 
    '[[DEEWR ID]]' as AGEID,
    eaed.terms as COLLECTIONS,
    eaed.year_level as YEAR_LEVEL,
    case when eaed.male = 1 then 'M' else 'F' end as GENDER,
    'I' as INDIGENOUS_STATUS,
    eaed.total_enrolment_days as ENROLMENT_DAYS,
    eaed.total_attendance_days as ATTENDANCE_DAYS,
    case when eaed.attendance_percentage < 90 then 1 else 0 end as ATTENDANCE_RATE_LESS_90,
    case when eaed.attendance_percentage >= 90 then 1 else 0 end as ATTENDANCE_RATE_MORE_90
    from add_per eaed
    where IND_MALE= 1 or IND_FEMALE = 1
    ),
    result2 as(
    select 
    '[[DEEWR ID]]' as AGEID,
    eaed.terms as COLLECTIONS,
    eaed.year_level as YEAR_LEVEL,
    case when eaed.male = 1 then 'M' else 'F' end as GENDER,
    'T' as INDIGENOUS_STATUS,
    eaed.total_enrolment_days as ENROLMENT_DAYS,
    eaed.total_attendance_days as ATTENDANCE_DAYS,
    case when eaed.attendance_percentage < 90 then 1 else 0 end as ATTENDANCE_RATE_LESS_90,
    case when eaed.attendance_percentage >= 90 then 1 else 0 end as ATTENDANCE_RATE_MORE_90
    from add_per eaed
    ),
    result as(
    select * from result1
    union all
    select * from result2
    )
    select 
    AGEID,
    result.COLLECTIONS as COLLECTIONS,
    'Y'||cast(YEAR_LEVEL as INTEGER) as YEAR_LEVEL,
    GENDER,
    INDIGENOUS_STATUS,
    sum(ENROLMENT_DAYS) as ENROLMENT_DAYS,
    sum(ATTENDANCE_DAYS) as ATTENDANCE_DAYS,
    sum(ATTENDANCE_RATE_LESS_90) as ATTENDANCE_RATE_LESS_90,
    sum(ATTENDANCE_RATE_MORE_90) as ATTENDANCE_RATE_MORE_90
    from result
    where result.collections is not null
    group by AGEID,result.COLLECTIONS,YEAR_LEVEL,GENDER,INDIGENOUS_STATUS
    order by INDIGENOUS_STATUS, cast(result.YEAR_LEVEL as INTEGER)