How to count schools on Uis Survey?

[Brian Lewis]

Page A12 of the UIS survey collects counts of schools by ISCED Level.

But pretty much every school teaches across multiple levels - e,g. a K12 school is ISCED0 ISCED1 ISCED2 and ISCED3!

How to we classify schools into ISCED1 ISCED2 ISCED3?

‌

[Brian Lewis]

@ghachey appreciate your thinking on this.

Possibilities:

1. school is assigned to the highest ISCED Level it teaches.
2. school is assigned to the ISCED Level in which it has maximum enrolment.
3. Fractional value is assigned to each ISCED Level pro-rated by enrolment, then summed and rounded.

This SQL implements option 2:

Select *, row_number() over (PARTITION By SchNo ORDER BY E Desc) RNFROM(

Select schNo, [ISCED Level], sum(Enrol) E

from warehouse.Enrol

INNER JOIN DimensionLevel DLON Enrol.ClassLevel = DL.LevelCode

GRoup by schNo, [ISCED Level]) UORDER BY schNo, [ISCED Level]

Select *FROM(Select *, row_number() over (PARTITION By SchNo ORDER BY E Desc) RNFROM(

Select schNo, [ISCED Level], sum(Enrol) E

from warehouse.Enrol

INNER JOIN DimensionLevel DLON Enrol.ClassLevel = DL.LevelCode

GRoup by schNo, [ISCED Level]) U) U2WHERE RN = 1ORDER BY [ISCED Level]

Select [ISCED Level], count(*) NumSchoolsFROM(Select *, row_number() over (PARTITION By SchNo ORDER BY E Desc) RNFROM(

Select schNo, [ISCED Level], sum(Enrol) E

from warehouse.Enrol

INNER JOIN DimensionLevel DLON Enrol.ClassLevel = DL.LevelCode

GRoup by schNo, [ISCED Level]) U) U2WHERE RN = 1GROUP BY [ISCED Level]

‌

‌

‌

‌

[Brian Lewis]

oops - the sql above needs to group by year!

‌

[Ghislain Hachey]

@Brian Lewis I believe that a school is counted at a level as soon as it teaches that level and therefore the total count across all ISCED levels will be more then the total count of schools in the country.

That said, I think your options are also good to have (one of them maybe) but perhaps to provide with a parameter ?countOnceOnly with a default of False?!

[Brian Lewis]

Ok - well that’s simpler i guess. Also we can then use the W switch:

to indicate the duplication?

Is this something you could run by Unesco?

‌

[Brian Lewis]

fyi this query does the prorata calculation 3) above:

Select surveyYear
, [ISCED LEvel]
, sum(E) E
, sum(ProRate) NumSchools
FROM
(
Select Enrol.schNo, Enrol.surveyYear, [ISCED Level], sum(Enrol) E , Tot, convert(float,sum(Enrol) )/Tot ProRate

from warehouse.Enrol

INNER JOIN DimensionLevel DL
    ON Enrol.ClassLevel = DL.LevelCode
INNER JOIN 
(
    Select schNo
    , SurveyYear
    , Enrol TOT
    from warehouse.EnrolSchoolR
) T
ON Enrol.SchNo = T.schNo and Enrol.surveyYear = T.surveyYear
GRoup by Enrol.schNo, Enrol.SurveyYear ,[ISCED Level], Tot
) UU
GROUP BY surveyYear, [ISCED Level]

ORDER BY surveyYear, [ISCED Level]

‌

[Ghislain Hachey]

I’ll check with Greg from SPC on your 'W' switch suggestion.

[Ghislain Hachey]

@Brian Lewis It is confirmed by SPC/UIS. It is expected to have schools classified in multiple ISCED level in A12. So this should be our default and you do not need to specify the 'W' switch either. If you already have a working solution to classify schools in a single ISCED level as discussed above we can still keep it. You mentioned the UIS export does not use RESTful endpoints but uses backend calls directly. In that case, leave whatever code in the backend that is capable of handling classificiation into single ISCED level with a comment.

[Ghislain Hachey]

@Brian Lewis can this be resolved?

[Brian Lewis]

@Ghislain Hachey Checkins for #1130 resolve warehouse.uisA12 implements the calculation as defined by consultation with uis. Note the same logic - schools counted against each ISCED level they teach at - also applies on sheetA13, and the school audit sheet introduced in #1130 shows the ISCED levels for each school.