Skip to content

Instantly share code, notes, and snippets.

@neurotech
Created August 10, 2015 00:43
Show Gist options
  • Save neurotech/32ec65ddf8335dac836a to your computer and use it in GitHub Desktop.
Save neurotech/32ec65ddf8335dac836a to your computer and use it in GitHub Desktop.
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment