select t0.family_ident,ss.student_number,ss.first_name,ss.last_name,ss.grade_level,to_char(ss.DISTRICTENTRYDATE,'MM/DD/YYYY'),to_char(ust.CUST_VISAEXPIRE,'MM/DD/YYYY'),to_char(ust.CUST_PASSPORTEXPIRE,'MM/DD/YYYY'),usf.CUST_FATHEREMAIL,usf.CUST_FATHERMOBILEPHONE,usf.CUST_MOTHEREMAIL,usf.CUST_MOTHERMOBILEPHONE
from students ss
join
(
WITH CORE AS
(
SELECT T0.FAMILY_IDENT,T0.ESSTUD,T0.MSSTUD,T0.HSSTUD,T0.SUMSTUD,
(case when (T0.ESSTUD<>0 AND T0.MSSTUD=0 AND T0.HSSTUD=0) THEN 1
when (T0.ESSTUD<>0 AND T0.MSSTUD<>0 AND T0.HSSTUD=0) THEN 2
when (T0.ESSTUD=0 AND T0.MSSTUD<>0 AND T0.HSSTUD=0) THEN 3
when (T0.ESSTUD=0 AND T0.MSSTUD<>0 AND T0.HSSTUD<>0) THEN 4
when (T0.ESSTUD=0 AND T0.MSSTUD=0 AND T0.HSSTUD<>0) THEN 5
when (T0.ESSTUD<>0 AND T0.MSSTUD=0 AND T0.HSSTUD<>0) THEN 6
when (T0.ESSTUD<>0 AND T0.MSSTUD<>0 AND T0.HSSTUD<>0) THEN 7
END
) AS FMTYPE
FROM
(SELECT family_ident,
count( CASE WHEN grade_level<=5 THEN 1
ELSE null END) as ESstud,
count( CASE WHEN grade_level>=6 and grade_level<=8 THEN 1
ELSE null END) as MSstud,
count( CASE WHEN grade_level>=9 THEN 1
ELSE null END) as HSstud,
COUNT(student_number) as Sumstud
FROM students
where ENROLL_STATUS='0'
GROUP BY family_ident) T0
order by FMTYPE DESC
),
ROWinfo as
(
select to_char(MAXROW) as MAXROW, to_char(CHECKPOINT1) as CHECKPOINT1 ,to_char(CHECKPOINT2) as CHECKPOINT2
from
(
(
select count(family_ident) as MAXROW, ROUND(count(family_ident)/3,0) AS CHECKPOINT1,ROUND(count(family_ident)*2/3,0) AS CHECKPOINT2
from (SELECT family_ident,
count( CASE WHEN grade_level<=5 THEN 1
ELSE null END) as ESstud,
count( CASE WHEN grade_level>=6 and grade_level<=8 THEN 1
ELSE null END) as MSstud,
count( CASE WHEN grade_level>=9 THEN 1
ELSE null END) as HSstud,
COUNT(student_number) as Sumstud
FROM students
where ENROLL_STATUS='0'
GROUP BY family_ident)
)
)
)
--CORE prepare all basic data for all unit--
--ROWinfo set 1/3checkpoint and 2/3 checkpoint--
select RNUM,FAMILY_IDENT,ESSTUD,MSSTUD,HSSTUD,SUMSTUD,fmtype
from
(SELECT count(1)over(order by core.FMTYPE,core.FAMILY_IDENT) as RNUM,core.FAMILY_IDENT as FAMILY_IDENT,core.ESSTUD as ESSTUD,core.MSSTUD as MSSTUD,core.HSSTUD as HSSTUD,core.SUMSTUD as SUMSTUD,core.fmtype as fmtype from core) trow
--Give this group a new rownumber for group all family--
where
(case
when FMtype=1
then 1
end) =1
or
(case
when RNUM<=(select checkpoint1 from rowinfo) and FMtype=2
then 1
end) =1
) t0
on ss.family_ident=t0.family_ident
and (ss.DISTRICTENTRYDATE>'16-DEC-2016' or ss.DISTRICTENTRYDATE='01-JAN-1900' )
join U_DEF_EXT_STUDENTS ust
on ss.DCID=ust.STUDENTSDCID
and (ust.CUST_VISAEXPIRE<='16-NOV-2016'
or ust.CUST_PASSPORTEXPIRE<='16-NOV-2016'
or (ust.CUST_VISAEXPIRE=null and ss.SCHOOLENTRYDATE<='15-AUG-2016')
or (ust.CUST_PASSPORTEXPIRE=null and ss.SCHOOLENTRYDATE<='15-AUG-2016'))
and ss.enroll_status='0'
join PS.U_STUDENTSUSERFIELDS usf
on ss.DCID=usf.STUDENTSDCID
order by ss.family_ident,ss.grade_level,ss.first_name,ss.student_number;
