clean sch

clean-sch.sql

CREATE TABLE clean_sch AS 
SELECT 
nt.*,
c.stnam stnam07,
c.fipst fipst07,
schnam07,
c.leanm07,		
c.leaid leaid07,
c.ncessch ncessch07,
cast(round((all_math04pctprof_0708 * 100), 1) as text)||'%' all_math04_0708,
cast(round((all_math08pctprof_0708 * 100), 1) as text)||'%' all_math08_0708,
cast(round((all_math10pctprof_0708 * 100), 1) as text)||'%' all_math10_0708,
cast(round((all_math12pctprof_0708 * 100), 1) as text)||'%' all_math12_0708,
cast(round((all_read04pctprof_0708 * 100), 1) as text)||'%' all_read04_0708, 
cast(round((all_read08pctprof_0708 * 100), 1) as text)||'%' all_read08_0708,
cast(round((all_read10pctprof_0708 * 100), 1) as text)||'%' all_read10_0708, 
cast(round((all_read12pctprof_0708 * 100), 1) as text)||'%' all_read12_0708
FROM 
(SELECT
a.stnam stnam09,
a.fipst fipst09,
schnam09,
a.leanm09,
a.leaid leaid09,
a.ncessch ncessch09,
b.stnam stnam08,
b.fipst fipst08,
schnam08,
b.leanm08,
b.leaid leaid08,
b.ncessch ncessch08,
cast(round((a.all_math04pctprof_0910 * 100), 1) as text)||'%' all_math04_0910,
cast(round((a.all_math08pctprof_0910 * 100), 1) as text)||'%' all_math08_0910,
cast(round((a.all_math10pctprof_0910 * 100), 1) as text)||'%' all_math10_0910,
cast(round((a.all_math12pctprof_0910 * 100), 1) as text)||'%' all_math12_0910,
cast(round((a.all_read04pctprof_0910 * 100), 1) as text)||'%' all_read04_0910, 
cast(round((a.all_read08pctprof_0910 * 100), 1) as text)||'%' all_read08_0910,
cast(round((a.all_read10pctprof_0910 * 100), 1) as text)||'%' all_read10_0910, 
cast(round((a.all_read12pctprof_0910 * 100), 1) as text)||'%' all_read12_0910,

cast(round((all_math04pctprof_0809 * 100), 1) as text)||'%' all_math04_0809,
cast(round((all_math08pctprof_0809 * 100), 1) as text)||'%' all_math08_0809,
cast(round((all_math10pctprof_0809 * 100), 1) as text)||'%' all_math10_0809,
cast(round((all_math12pctprof_0809 * 100), 1) as text)||'%' all_math12_0809,
cast(round((all_read04pctprof_0809 * 100), 1) as text)||'%' all_read04_0809, 
cast(round((all_read08pctprof_0809 * 100), 1) as text)||'%' all_read08_0809,
cast(round((all_read10pctprof_0809 * 100), 1) as text)||'%' all_read10_0809,  
cast(round((all_read12pctprof_0809 * 100), 1) as text)||'%' all_read12_0809,

cast(round((l.all_math04pctprof_0910 * 100), 1) as text)||'%' lea_math04_0910,
cast(round((l.all_math08pctprof_0910 * 100), 1) as text)||'%' lea_math08_0910,
cast(round((l.all_math10pctprof_0910 * 100), 1) as text)||'%' lea_math10_0910,
cast(round((l.all_math12pctprof_0910 * 100), 1) as text)||'%' lea_math12_0910,
cast(round((l.all_read04pctprof_0910 * 100), 1) as text)||'%' lea_read04_0910, 
cast(round((l.all_read08pctprof_0910 * 100), 1) as text)||'%' lea_read08_0910,
cast(round((l.all_read10pctprof_0910 * 100), 1) as text)||'%' lea_read10_0910, 
cast(round((l.all_read12pctprof_0910 * 100), 1) as text)||'%' lea_read12_0910
FROM sch_0910 a
LEFT JOIN sch_0809 b on ncessch09 = ncessch08
LEFT JOIN lea_0910 l on leaid09 = l.leaid) nt
LEFT JOIN sch_0708 c on ncessch09 = ncessch07
;