Spreadsheet: 11694423-aa5c-4a2e-9c00-a110b3e03eb6
Sheet: SQL

PUBLIC SCHOOLS |  |  |  |  |  |  |  |  |  |  |  |  |  | CHARTER SCHOOLS |  |  |  |  |  |  |  |  |  |  |  |  |  | TO [NAME]
(SELECT 1, |  |  |  |  |  |  |  |  |  |  |  |  |  | SELECT a.charter_flag as 'CHARTER', |  |  |  |  |  |  |  |  |  |  |  |  |  | select case when v.grade_level_summary = 'PREK' then 1 ELSE 0 END as 'PREK',
 |  | a.institution_name as 'LOCAL EDUCATION AGENCY', |  |  |  |  |  |  |  |  |  |  |  |  |  | a.PREK, |  |  |  |  |  |  |  |  |  |  |  |  | case when v.grade_level_summary = '[NAME]' then 1 Else 0 END as 'ELEMENTARY',
 |  | a.PREK, |  |  |  |  |  |  |  |  |  |  |  |  |  | [NAME], |  |  |  |  |  |  |  |  |  |  |  |  | case when v.grade_level_summary = 'MID' then 1 Else 0 END as 'MIDDLE',
 |  | [NAME], |  |  |  |  |  |  |  |  |  |  |  |  |  | a.MIDDLE, |  |  |  |  |  |  |  |  |  |  |  |  | case when v.grade_level_summary = 'HIGH' then 1 Else 0 END as 'HIGH',
 |  | a.MIDDLE, |  |  |  |  |  |  |  |  |  |  |  |  |  | a.HIGH, |  |  |  |  |  |  |  |  |  |  |  |  | case when y.grade_low <= 0 AND y.grade_high = 12 then 1 else 0 END as 'K12',
 |  | a.HIGH, |  |  |  |  |  |  |  |  |  |  |  |  |  | [NAME], |  |  |  |  |  |  |  |  |  |  |  |  |  | i.institution_name
 |  | [NAME], |  |  |  |  |  |  |  |  |  |  |  |  |  | a.PREK+a.ELEMENTARY+a.MIDDLE+a.HIGH as 'TOTAL REGULAR EDUCATION', |  |  |  |  |  |  |  |  |  |  |  | from v_be_school v
 |  | a.PREK+a.ELEMENTARY+a.MIDDLE+a.HIGH as 'TOTAL REGULAR EDUCATION', |  |  |  |  |  |  |  |  |  |  |  |  |  | [NAME], |  |  |  |  |  |  |  |  |  |  |  |  | join v_be_institution i
 |  | [NAME], |  |  |  |  |  |  |  |  |  |  |  |  |  | [NAME], |  |  |  |  |  |  |  |  |  |  |  |  | on v.district_id=i.institution_id
 |  | [NAME], |  |  |  |  |  |  |  |  |  |  |  |  |  | [NAME], |  |  |  |  |  |  |  |  |  |  |  |  | left outer join v_be_school_year y
 |  | [NAME], |  |  |  |  |  |  |  |  |  |  |  |  |  | [NAME], |  |  |  |  |  |  |  |  |  |  |  |  | on v.school_id=y.school_id
 |  | [NAME], |  |  |  |  |  |  |  |  |  |  |  |  |  | [NAME], |  |  |  |  |  |  |  |  |  |  |  |  | AND v.district_id=y.district_id
 |  | [NAME], |  |  |  |  |  |  |  |  |  |  |  |  |  | [NAME].[NAME].ALTERNATEEDUCATION+b.YIC as 'TOTAL SPECIAL PURPOSE', |  |  |  |  |  |  |  |  |  |  |  |  | AND v.school_nbr=y.school_nbr
 |  | [NAME].[NAME].ALTERNATEEDUCATION+b.YIC as 'TOTAL SPECIAL PURPOSE', |  |  |  |  |  |  |  |  |  |  |  |  |  | PREK+a.ELEMENTARY+a.MIDDLE+a.HIGH+SPECIALEDUCATION+b.VOCATIONALEDUCATION+ |  |  |  |  |  |  |  |  |  |  |  |  | AND y.school_year=2013
 |  | PREK+a.ELEMENTARY+a.MIDDLE+a.HIGH+SPECIALEDUCATION+b.VOCATIONALEDUCATION+ |  |  |  |  |  |  |  |  |  |  |  |  |  |  | b.ALTERNATEEDUCATION+b.YIC as 'GRAND TOTAL' |  |  |  |  |  |  |  |  |  |  | where v.date_opened < '7/1/2013'
 |  |  | b.ALTERNATEEDUCATION+b.YIC as 'GRAND TOTAL' |  |  |  |  |  |  |  |  |  |  | FROM |  |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.date_closed > '6/30/2012'
FROM |  |  |  |  |  |  |  |  |  |  |  |  |  |  | (select SUM (case when v.grade_level_summary = 'PREK' then 1 ELSE 0 END) as 'PREK', |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.school_type = 1
 | (select SUM (case when v.grade_level_summary = 'PREK' then 1 ELSE 0 END) as 'PREK', |  |  |  |  |  |  |  |  |  |  |  |  |  |  | SUM (case when v.grade_level_summary = '[NAME]' then 1 Else 0 END) as '[NAME]', |  |  |  |  |  |  |  |  |  |  |  |  | AND v.private_flag = 0
 |  | SUM (case when v.grade_level_summary = '[NAME]' then 1 Else 0 END) as '[NAME]', |  |  |  |  |  |  |  |  |  |  |  |  |  | SUM (case when v.grade_level_summary = 'MID' then 1 Else 0 END) as 'MIDDLE', |  |  |  |  |  |  |  |  |  |  |  |  | AND v.online_flag = 0
 |  | SUM (case when v.grade_level_summary = 'MID' then 1 Else 0 END) as 'MIDDLE', |  |  |  |  |  |  |  |  |  |  |  |  |  | SUM (case when v.grade_level_summary = 'HIGH' then 1 Else 0 END) as 'HIGH', |  |  |  |  |  |  |  |  |  |  |  |  | AND v.virtual_flag = 0
 |  | SUM (case when v.grade_level_summary = 'HIGH' then 1 Else 0 END) as 'HIGH', |  |  |  |  |  |  |  |  |  |  |  |  |  | sum (case when y.grade_low <= 0 AND y.grade_high = 12 then 1 else 0 END) as 'K12',
 |  | sum (case when y.grade_low <= 0 AND y.grade_high = 12 then 1 else 0 END) as 'K12', |  |  |  |  |  |  |  |  |  |  |  |  |  | v.charter_flag
 |  | i.institution_name |  |  |  |  |  |  |  |  |  |  |  | from v_be_school v
from v_be_school v |  |  |  |  |  |  |  |  |  |  |  |  |  |  | join v_be_institution i
 | join v_be_institution i |  |  |  |  |  |  |  |  |  |  |  |  |  | on v.district_id=i.institution_id
 | on v.district_id=i.institution_id |  |  |  |  |  |  |  |  |  |  |  |  |  | left outer join v_be_school_year y
 | left outer join v_be_school_year y |  |  |  |  |  |  |  |  |  |  |  |  |  | on v.school_id=y.school_id
 | on v.school_id=y.school_id |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.district_id=y.district_id
 | AND v.district_id=y.district_id |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.school_nbr=y.school_nbr
 | AND v.school_nbr=y.school_nbr |  |  |  |  |  |  |  |  |  |  |  |  |  | AND y.school_year=2013
 | AND y.school_year=2013 |  |  |  |  |  |  |  |  |  |  |  |  | where v.date_opened < '7/1/2013'
where v.date_opened < '7/1/2013' |  |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.date_closed > '6/30/2012'
 | AND v.date_closed > '6/30/2012' |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.school_type = 1
 | AND v.school_type = 1 |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.private_flag = 0
 | AND v.private_flag = 0 |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.charter_flag = 1
 | AND v.charter_flag = 0 |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.online_flag = 0
 | AND v.online_flag = 0 |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.virtual_flag = 0
 | AND v.virtual_flag = 0 |  |  |  |  |  |  |  |  |  |  |  |  | group by v.charter_flag)
group by i.institution_name) |  |  |  |  |  |  |  |  |  |  |  |  |  | AS a
AS a |  |  |  |  |  |  |  |  |  |  |  |  |  | LEFT OUTER JOIN
LEFT OUTER JOIN |  |  |  |  |  |  |  |  |  |  |  |  |  | (
( |  |  |  |  |  |  |  |  |  |  |  |  |  | select | SUM (case when v.school_type = 2 then 1 ELSE 0 END) as 'SPECIALEDUCATION',
select i.institution_name | , |  |  |  |  |  |  |  |  |  |  |  |  |  |  | SUM (case when v.school_type = 3 then 1 Else 0 END) as 'VOCATIONALEDUCATION',
 |  | SUM (case when v.school_type = 2 then 1 ELSE 0 END) as 'SPECIALEDUCATION', |  |  |  |  |  |  |  |  |  |  |  |  |  | SUM (case when v.school_type = 4 then 1 Else 0 END) as 'ALTERNATEEDUCATION',
 |  | SUM (case when v.school_type = 3 then 1 Else 0 END) as 'VOCATIONALEDUCATION', |  |  |  |  |  |  |  |  |  |  |  |  |  | SUM (case when v.school_type = 5 then 1 Else 0 END) as 'YIC',
 |  | SUM (case when v.school_type = 4 then 1 Else 0 END) as 'ALTERNATEEDUCATION', |  |  |  |  |  |  |  |  |  |  |  |  |  | v.charter_flag
 |  | SUM (case when v.school_type = 5 then 1 Else 0 END) as 'YIC'
 |  |  |  |  |  |  |  |  |  |  |  |  |  | from v_be_school v
from v_be_school v |  |  |  |  |  |  |  |  |  |  |  |  |  |  | join v_be_institution i
 | join v_be_institution i |  |  |  |  |  |  |  |  |  |  |  |  |  | on v.district_id=i.institution_id
 | on v.district_id=i.institution_id |  |  |  |  |  |  |  |  |  |  |  |  |  | join v_be_school_year y
 | join v_be_school_year y |  |  |  |  |  |  |  |  |  |  |  |  |  | on v.school_id=y.school_id
 | on v.school_id=y.school_id |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.district_id=y.district_id
 | AND v.district_id=y.district_id |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.school_nbr=y.school_nbr
 | AND v.school_nbr=y.school_nbr |  |  |  |  |  |  |  |  |  |  |  |  |  | AND y.school_year=2012
 | AND y.school_year=2013 |  |  |  |  |  |  |  |  |  |  |  |  | where v.date_opened < '7/1/2012'
where v.date_opened < '7/1/2013' |  |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.date_closed > '6/30/2011'
 | AND v.date_closed > '6/30/2012' |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.private_flag = 0
 | AND v.private_flag = 0 |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.school_type not in ('0','6','7')
 | AND v.school_type not in ('0','6','7') |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.charter_flag = 1
 | AND v.charter_flag = 0 |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.online_flag = 0
 | AND v.online_flag = 0 |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.virtual_flag = 0
 | AND v.virtual_flag = 0 |  |  |  |  |  |  |  |  |  |  |  |  |  | group by v.charter_flag)
 | AND v.district_id not in ('1296','5522','186050') |  |  |  |  |  |  |  |  |  |  |  |  | AS b
group by i.institution_name) |  |  |  |  |  |  |  |  |  |  |  |  |  | on a.charter_flag=b.charter_flag
AS b |  |  |  |  |  |  |  |  |  |  |  |  |  | LEFT OUTER JOIN
ON a.institution_name=b.institution_name |  |  |  |  |  |  |  |  |  |  |  |  |  | (select SUM (case when v.virtual_flag = 1 OR v.online_flag=1 then 1 ELSE 0 END) as 'VIRTUAL',
LEFT OUTER JOIN |  |  |  |  |  |  |  |  |  |  |  |  |  |  | v.charter_flag
(select i.institution_name, |  |  |  |  |  |  |  |  |  |  |  |  |  | from v_be_school v
 |  | SUM (case when v.virtual_flag = 1 OR v.online_flag=1 then 1 ELSE 0 END) as 'VIRTUAL' |  |  |  |  |  |  |  |  |  |  |  |  | join v_be_institution i
from v_be_school v |  |  |  |  |  |  |  |  |  |  |  |  |  |  | on v.district_id=i.institution_id
 | join v_be_institution i |  |  |  |  |  |  |  |  |  |  |  |  |  | join v_be_school_year y
 | on v.district_id=i.institution_id |  |  |  |  |  |  |  |  |  |  |  |  |  | on v.school_id=y.school_id
 | join v_be_school_year y |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.district_id=y.district_id
 | on v.school_id=y.school_id |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.school_nbr=y.school_nbr
 | AND v.district_id=y.district_id |  |  |  |  |  |  |  |  |  |  |  |  |  | AND y.school_year=2013
 | AND v.school_nbr=y.school_nbr |  |  |  |  |  |  |  |  |  |  |  |  | where v.date_opened < '7/1/2013'
 | AND y.school_year=2013 |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.date_closed > '6/30/2012'
where v.date_opened < '7/1/2013' |  |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.private_flag = 0
 | AND v.date_closed > '6/30/2012' |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.school_type not in ('0','6','7')
 | AND v.private_flag = 0 |  |  |  |  |  |  |  |  |  |  |  |  |  | AND v.charter_flag = 1
 | AND v.school_type not in ('0','6','7') |  |  |  |  |  |  |  |  |  |  |  |  |  | group by v.charter_flag)
 | AND v.charter_flag = 0 |  |  |  |  |  |  |  |  |  |  |  |  | as c
 | AND v.district_id not in ('1296','5522','186050','186166') |  |  |  |  |  |  |  |  |  |  |  |  | on b.charter_flag=c.charter_flag
group by i.institution_name)
as c
on b.institution_name = c.institution_name
union
SELECT 2,
 |  | b.institution_name as 'LOCAL EDUCATION AGENCY',
 |  | a.PREK,
 |  | [NAME],
 |  | a.MIDDLE,
 |  | a.HIGH,
 |  | [NAME],
 |  | a.PREK+a.ELEMENTARY+a.MIDDLE+a.HIGH as 'TOTAL REGULAR EDUCATION',
 |  | [NAME],
 |  | [NAME],
 |  | [NAME],
 |  | [NAME],
 |  | [NAME],
 |  | [NAME].[NAME].ALTERNATEEDUCATION+b.YIC as 'TOTAL SPECIAL PURPOSE',
 |  | SPECIALEDUCATION+b.VOCATIONALEDUCATION+ | b.ALTERNATEEDUCATION+b.YIC as 'GRAND TOTAL'
FROM
 | (select SUM (case when v.grade_level_summary = 'PREK' then 1 ELSE 0 END) as 'PREK',
 |  | SUM (case when v.grade_level_summary = '[NAME]' then 1 Else 0 END) as '[NAME]',
 |  | SUM (case when v.grade_level_summary = 'MID' then 1 Else 0 END) as 'MIDDLE',
 |  | SUM (case when v.grade_level_summary = 'HIGH' then 1 Else 0 END) as 'HIGH',
 |  | sum (case when y.grade_low <= 0 AND y.grade_high = 12 then 1 else 0 END) as 'K12',
 |  | i.institution_name
from v_be_school v
 | join v_be_institution i
 | on v.district_id=i.institution_id
 | left outer join v_be_school_year y
 | on v.school_id=y.school_id
 | AND v.district_id=y.district_id
 | AND v.school_nbr=y.school_nbr
 | AND y.school_year=2013
where v.date_opened < '7/1/2013'
 | AND v.date_closed > '6/30/2012'
 | AND v.school_type = 1
 | AND v.district_id = 1296
group by i.institution_name)
AS a
RIGHT OUTER JOIN
(
select i.institution_name | ,
 |  | SUM (case when v.school_type = 2 then 1 ELSE 0 END) as 'SPECIALEDUCATION',
 |  | SUM (case when v.school_type = 3 then 1 Else 0 END) as 'VOCATIONALEDUCATION',
 |  | SUM (case when v.school_type = 4 then 1 Else 0 END) as 'ALTERNATEEDUCATION',
 |  | SUM (case when v.school_type = 5 then 1 Else 0 END) as 'YIC'
from v_be_school v
 | join v_be_institution i
 | on v.district_id=i.institution_id
 | join v_be_school_year y
 | on v.school_id=y.school_id
 | AND v.district_id=y.district_id
 | AND v.school_nbr=y.school_nbr
 | AND y.school_year=2013
where v.date_opened < '7/1/2013'
 | AND v.date_closed > '6/30/2012'
 | AND v.private_flag = 0
 | AND v.school_type not in ('0','6','7')
 | AND v.district_id = 1296
group by i.institution_name)
AS b
ON a.institution_name=b.institution_name
LEFT OUTER JOIN
(select i.institution_name,
 |  | SUM (case when v.virtual_flag = 1 OR v.online_flag=1 then 1 ELSE 0 END) as 'VIRTUAL'
from v_be_school v
 | join v_be_institution i
 | on v.district_id=i.institution_id
 | join v_be_school_year y
 | on v.school_id=y.school_id
 | AND v.district_id=y.district_id
 | AND v.school_nbr=y.school_nbr
 | AND y.school_year=2013
where v.date_opened < '7/1/2013'
 | AND v.date_closed > '6/30/2012'
 | AND v.district_id = 1296
 | AND v.school_type not in ('0','6','7')
group by i.institution_name)
as c
on b.institution_name = c.institution_name
union
SELECT 3,
 |  | b.institution_name as 'LOCAL EDUCATION AGENCY',
 |  | a.PREK,
 |  | [NAME],
 |  | a.MIDDLE,
 |  | a.HIGH,
 |  | [NAME],
 |  | a.PREK+a.ELEMENTARY+a.MIDDLE+a.HIGH as 'TOTAL REGULAR EDUCATION',
 |  | [NAME],
 |  | [NAME],
 |  | [NAME],
 |  | [NAME],
 |  | [NAME],
 |  | [NAME].[NAME].ALTERNATEEDUCATION+b.YIC as 'TOTAL SPECIAL PURPOSE',
 |  | PREK+a.ELEMENTARY+a.MIDDLE+a.HIGH+SPECIALEDUCATION+b.VOCATIONALEDUCATION+
 |  |  | b.ALTERNATEEDUCATION+b.YIC as 'GRAND TOTAL'
FROM
 | (select SUM (case when v.grade_level_summary = 'PREK' then 1 ELSE 0 END) as 'PREK',
 |  | SUM (case when v.grade_level_summary = '[NAME]' then 1 Else 0 END) as '[NAME]',
 |  | SUM (case when v.grade_level_summary = 'MID' then 1 Else 0 END) as 'MIDDLE',
 |  | SUM (case when v.grade_level_summary = 'HIGH' then 1 Else 0 END) as 'HIGH',
 |  | sum (case when y.grade_low <= 0 AND y.grade_high = 12 then 1 else 0 END) as 'K12',
 |  | i.institution_name
from v_be_school v
 | join v_be_institution i
 | on v.district_id=i.institution_id
 | left outer join v_be_school_year y
 | on v.school_id=y.school_id
 | AND v.district_id=y.district_id
 | AND v.school_nbr=y.school_nbr
 | AND y.school_year=2013
where v.date_opened < '7/1/2013'
 | AND v.date_closed > '6/30/2012'
 | AND v.school_type = 1
 | AND v.district_id = 186166
group by i.institution_name)
AS a
LEFT OUTER JOIN
(
select i.institution_name | ,
 |  | SUM (case when v.school_type = 2 then 1 ELSE 0 END) as 'SPECIALEDUCATION',
 |  | SUM (case when v.school_type = 3 then 1 Else 0 END) as 'VOCATIONALEDUCATION',
 |  | SUM (case when v.school_type = 4 then 1 Else 0 END) as 'ALTERNATEEDUCATION',
 |  | SUM (case when v.school_type = 5 then 1 Else 0 END) as 'YIC'
from v_be_school v
 | join v_be_institution i
 | on v.district_id=i.institution_id
 | join v_be_school_year y
 | on v.school_id=y.school_id
 | AND v.district_id=y.district_id
 | AND v.school_nbr=y.school_nbr
 | AND y.school_year=2013
where v.date_opened < '7/1/2013'
 | AND v.date_closed > '6/30/2012'
 | AND v.private_flag = 0
 | AND v.school_type not in ('0','6','7')
 | AND v.district_id = 186166
group by i.institution_name)
AS b
ON a.institution_name=b.institution_name
LEFT OUTER JOIN
(select i.institution_name,
 |  | SUM (case when v.virtual_flag = 1 OR v.online_flag=1 then 1 ELSE 0 END) as 'VIRTUAL'
from v_be_school v
 | join v_be_institution i
 | on v.district_id=i.institution_id
 | join v_be_school_year y
 | on v.school_id=y.school_id
 | AND v.district_id=y.district_id
 | AND v.school_nbr=y.school_nbr
 | AND y.school_year=2013
where v.date_opened < '7/1/2013'
 | AND v.date_closed > '6/30/2012'
 | AND v.district_id = 186166
 | AND v.school_type not in ('0','6','7')
group by i.institution_name)
as c
on b.institution_name = c.institution_name)
order by 1