National Standard Performance Accreditation Progress Query Fix

SELECT T1.District
    ,T2.[Total Schools]
    ,T3.[Total Schools Evaluated]
    ,T1.[Total Schools Accredited]
    ,T1.[Level 1]
    ,T1.[Level 2]
    ,T1.[Level 3]
    ,T1.[Level 4]
FROM (
    SELECT [dName] AS District
        --    ,[inspsetName]
        --    ,[inspsetYear]
        ,SUM(CASE 
                WHEN [saSchLevel] = 'Level 1'
                    THEN 1
                ELSE 0
                END) AS 'Level 1'
        ,SUM(CASE 
                WHEN [saSchLevel] = 'Level 2'
                    THEN 1
                ELSE 0
                END) AS 'Level 2'
        ,SUM(CASE 
                WHEN [saSchLevel] = 'Level 3'
                    THEN 1
                ELSE 0
                END) AS 'Level 3'
        ,SUM(CASE 
                WHEN [saSchLevel] = 'Level 4'
                    THEN 1
                ELSE 0
                END) AS 'Level 4'
        ,SUM(CASE 
                WHEN [saSchLevel] = 'Level 2'
                    THEN 1
                ELSE 0
                END) + SUM(CASE 
                WHEN [saSchLevel] = 'Level 3'
                    THEN 1
                ELSE 0
                END) + SUM(CASE 
                WHEN [saSchLevel] = 'Level 4'
                    THEN 1
                ELSE 0
                END) AS 'Total Schools Accredited'
    FROM (SELECT dName, saSchLevel,
               ROW_NUMBER() OVER (PARTITION BY Schools.schNo ORDER BY inspsetYear DESC) AS rank
               FROM [SchoolAccreditation]
    INNER JOIN [SchoolInspection] ON [SchoolAccreditation].saID = [SchoolInspection].inspID
    INNER JOIN [InspectionSet] ON [InspectionSet].inspsetID = [SchoolInspection].inspsetID
    INNER JOIN [Schools] ON [SchoolInspection].schNo = [Schools].schNo
    INNER JOIN [Islands] ON [Schools].iCode = [Islands].iCode
    INNER JOIN [Districts] ON [Islands].iGroup = [Districts].dID
    ) x
    WHERE x.rank = 1
    GROUP BY [dName]
    ) T1
INNER JOIN (
    -- Total schools in EMIS grouped by district
    SELECT [dName] AS District
        ,COUNT(*) AS 'Total Schools'
    FROM [Schools]
    INNER JOIN [Islands] ON [Schools].iCode = [Islands].iCode
    INNER JOIN [Districts] ON [Islands].iGroup = [Districts].dID
    WHERE Schools.schClosed = 0
    GROUP BY [dName]
    ) T2 ON T1.District = T2.District
INNER JOIN (
    -- Total schools in EMIS with (at least) an school accreditation inspection
        SELECT  [dName] AS District
        ,COUNT(*) AS 'Total Schools Evaluated'
  FROM (SELECT [dName]
    ,Schools.schNo
    ,inspsetYear,
               ROW_NUMBER() OVER (PARTITION BY Schools.schNo ORDER BY inspsetYear DESC) AS rank
          FROM [SchoolAccreditation]
    INNER JOIN [SchoolInspection] ON [SchoolAccreditation].saID = [SchoolInspection].inspID
    INNER JOIN [InspectionSet] ON [InspectionSet].inspsetID = [SchoolInspection].inspsetID
    INNER JOIN [Schools] ON [SchoolInspection].schNo = [Schools].schNo
    INNER JOIN [Islands] ON [Schools].iCode = [Islands].iCode
    INNER JOIN [Districts] ON [Islands].iGroup = [Districts].dID) x
WHERE x.rank = 1
GROUP BY [dName]
    ) T3 ON T1.District = T3.District
Comments (2)
