--SSR SQL --Please note that FTE and Stuload fields used throughout this script are percentages. Within the script /100 reduces this to represent 1 FTE. Dividing by 200 halves the FTE, used for students on a placement year. --Student component including cost centre --Section 1 – this is for the student FTE excluding franchised & placement students, and those abroad for the year who are not studying for any portion of the year. Select 'stud_FTE_basic' SSR_constituent, s.f_xinstid01, co.f_costcn, sum(co.f_xfte01/100)f_value --calculates the FTE of this constituent from C13051 s inner join C13051_co co on (s.f_instancekey=co.f_instancekey) inner join C13051_Derive dv on (s.f_instancekey=dv.f_instancekey) where s.f_xpses01 in ('1','2') --excludes those who are not counted within the HE or FE session population and nvl(s.f_locsdy,'ZZ')!='D' --excludes students who are on industrial (or other) placement for the year as a whole and nvl(dv.f_zpropfran,'0')='0' --excludes franchised students (whose proportion franchised is not 0) and dv.f_zssrmob_marker!='1' --excludes those who are “abroad for the whole year and not studying for any portion of the year” group by 'stud_FTE_basic', s.f_xinstid01, co.f_costcn union all --Section 2 – this is for the student FTE for franchised & placement students. Select 'stud_FTE_placemt'SSR_constituent, s.f_xinstid01, co.f_costcn, sum (decode(s.f_locsdy,'D',((co.f_xfte01/200)*(1-nvl(dv.f_zpropfran,'0'))), -- Where the student is on placement for the year as a whole, the xfte01 is divided by 200, and then multiplied by 1 minus the proportion franchised decode(dv.f_zssrmob_marker,'1',((co.f_xfte01/200)*(1-nvl(dv.f_zpropfran,'0'))), -- Where the student is abroad for the year AND not studying for any proportion of the year, the xfte01 is divided by 200, and then multiplied by 1 minus the proportion franchised ((co.f_xfte01/100)*(1-nvl(dv.f_zpropfran,'0'))))))f_value -- Where students are neither on placement or identified by the mobility marker, the xfte01 is divided by 100 and then multiplied by 1 minus the proportion franchised from C13051 s inner join C13051_co co on (s.f_instancekey=co.f_instancekey) inner join c13051_derive dv on (s.f_instancekey=dv.f_instancekey) where s.f_xpses01 in ('1','2') --excludes students who are not counted within the HE or FE session population and (nvl(dv.f_zpropfran,0) not in (0,1) or nvl(s.f_locsdy,'ZZ')='D' or dv.f_zssrmob_marker='1') --excludes students who are 100% or 0% franchised, or who do not have a location of study of D, or who are not “abroad for the whole year and not studying for any portion of the year” group by 'stud_FTE_placemt', s.f_xinstid01, co.f_costcn --Student component excluding cost centre --Section 1 – this is for the student FTE excluding franchised & placement students, and those abroad for the year who are not studying for any portion of the year. select 'stud_FTE_basic' SSR_constituent, s.f_xinstid01, sum(to_number(s.f_stuload)/100) f_total --calculates the FTE of this constituent. from C13051 s inner join C13051_Derive dv on (s.f_instancekey=dv.f_instancekey) where s.f_xpses01 in ('1','2') --excludes students who are not counted within the HE or FE session population and nvl(dv.f_zpropfran,0)=0 --excludes franchised students (the proportion of the FTE that is franchised is not 0). and nvl(s.f_locsdy,'ZZ')!='D' --excludes students who are on placement and dv.f_zssrmob_marker!='1' --excludes those who are “abroad for the whole year and not studying for any portion of the year” and nvl(s.f_stuload,0) !=0 --excludes students with null or 0 stuload group by 'stud_FTE_basic' , s.f_xinstid01 union all --Section 2 – this is for the student FTE for franchised & placement students. select 'stud_FTE_placemt' SSR_constituent, s.f_xinstid01, sum (decode(s.f_locsdy,'D',((s.f_stuload/200)*(1-nvl(dv.f_zpropfran,'0'))), -- Where the student is on placement for the year as a whole, the stuload is divided by 200, and then multiplied by 1 minus the proportion franchised decode(dv.f_zssrmob_marker,'1',((s.f_stuload/200)*(1-nvl(dv.f_zpropfran,'0'))), -- Where the student is abroad for the year AND not studying for any proportion of the year, the stuload is divided by 200, and then multiplied by 1 minus the proportion franchised ((s.f_stuload/100)*(1-nvl(dv.f_zpropfran,'0'))))))f_value -- Where students are neither on placement or identified by the mobility marker, the stuload is divided by 100 and then multiplied by 1 minus the proportion franchised from C13051 s inner join C13051_Derive dv on (s.f_instancekey=dv.f_instancekey) where s.f_xpses01 in ('1','2') --excludes students who are not counted within the HE or FE session population and (nvl(dv.f_zpropfran,0) not in (0,1) or nvl(s.f_locsdy,'ZZ')='D' or dv.f_zssrmob_marker='1') --excludes students who are 100% or 0% franchised, or who do not have a location of study of D, or who are not “abroad for the whole year and not studying for any portion of the year” and nvl(s.f_stuload,0)!=0 --excludes students with null or 0 stuload group by 'stud_FTE_placemt', s.f_xinstid01 union all --Staff components data --Section 3. select 'staff_FTE' SSR_constituent, con.f_xinstid01, cc.f_ccentre f_costcn, --If cost centre data is not required this can be excluded sum(to_number(cc.f_xsfte01)/100) f_value -- calculates the FTE of this constituent from C13025_contract con inner join C13025_person p on (con.f_personkey=p.f_personkey) inner join C13025_cc cc on (con.f_contractkey=cc.f_contractkey) where con.f_xpsesc01='1' --excludes staff whose contracts are not counted within the session population and con.f_acempfun in ('1','3') --excludes staff whose academic employment function is neither 1 Teaching NOR 3 Teaching and research group by 'staff_FTE', con.f_xinstid01, cc.f_ccentre --if you wish to split out non-atypical and atypical staff --Section 3. select 'staff_FTE_nonatyp' SSR_constituent, con.f_xinstid01, cc.f_ccentre f_costcn, --If cost centre data is not required this can be excluded sum(to_number(cc.f_xsfte01)/100) f_value -- calculates the FTE of this constituent from C13025_contract con inner join C13025_person p on (con.f_personkey=p.f_personkey) inner join C13025_cc cc on (con.f_contractkey=cc.f_contractkey) where con.f_xpsesc01='1' --excludes staff whose contracts are not counted within the session population and con.f_acempfun in ('1','3') --excludes staff whose academic employment function is neither 1 Teaching NOR 3 Teaching and research and con.f_terms!='3' --excludes atypical staff group by 'staff_FTE_nonatyp', con.f_xinstid01, cc.f_ccentre union all --Section 4. select 'staff_FTE_atyp' SSR_constituent, con.f_xinstid01, cc.f_ccentre f_costcn, --If cost centre data is not required this can be excluded sum(to_number(cc.f_xsfte01)/100) f_value -- calculates the FTE of this constituent from C13025_contract con inner join C13025_person p on (con.f_personkey=p.f_personkey) inner join C13025_cc cc on (con.f_contractkey=cc.f_contractkey) where con.f_xpsesc01='1' --excludes staff whose contracts are not counted within the session population and con.f_acempfun in ('1','3') --excludes staff whose academic employment function is neither 1 Teaching NOR 3 Teaching and research and con.f_terms='3' -- excludes non-atypical staff group by 'staff_FTE_atyp', con.f_xinstid01, cc.f_ccentre --Staff component excluding cost centre does not require any additional changes beyond removing the cost centre field. --FTE calculation and restrictions for earlier years. For earlier years, the FTE calculation for the franchise & placement students is as follows: With cost centre: sum(decode(s.f_locsdy,'D',((co.f_xfte01/200)*(1-nvl(dv.f_zpropfran,'0'))),((co.f_xfte01/100)*(1-nvl(dv.f_zpropfran,'0')))))f_value -- calculates the FTE of this constituent. Where the student is on placement for the year as a whole, the xfte01 is divided by 200, and then multiplied by 1 minus the proportion franchised. Where the student is not on placement for the year as a whole, the xfte01 is divided by 100 and then multiplied by 1 minus the proportion franchised. where s.f_xpses01 in ('1','2') --excludes students who are not counted within the HE or FE session population and (nvl(dv.f_zpropfran,0) not in (0,1) or nvl(s.f_locsdy,'ZZ')='D') --excludes students who are 100% or 0% franchised, or who do not have a location of study of D Without cost centre: sum(to_number(decode(s.f_locsdy,'D',(s.f_stuload/200)*(1-nvl(dv.f_zpropfran,0)),(s.f_stuload/100)*(1-nvl(dv.f_zpropfran,0)))))f_total -- calculates the FTE of this constituent. Where the student is on placement for the year as a whole, the stuload is divided by 200, and then multiplied by 1 minus the proportion franchised. Where the student is not on placement for the year as a whole, the stuload is divided by 100 and then multiplied by 1 minus the proportion franchised. where s.f_xpses01 in ('1','2') --excludes students who are not counted within the HE or FE session population and (nvl(dv.f_zpropfran,0) not in (0,1) or nvl(s.f_locsdy,'ZZ')='D') --excludes students who are 100% or 0% franchised, or who do not have a location of study of D and nvl(s.f_stuload,0)!=0 --excludes students with null or 0 stuload