clean lea

clean-lea.sql

SELECT 
nt.*,
c.stnam stnam07,
c.fipst fipst07,
c.leanm07,
c.leaid leaid07,
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,
a.leanm09,
a.leaid leaid09,
b.stnam stnam08,
b.fipst fipst08,
b.leanm08,
b.leaid leaid08,
cast(round((all_math04pctprof_0910 * 100), 1) as text)||'%' all_math04_0910,
cast(round((all_math08pctprof_0910 * 100), 1) as text)||'%' all_math08_0910,
cast(round((all_math10pctprof_0910 * 100), 1) as text)||'%' all_math10_0910,
cast(round((all_math12pctprof_0910 * 100), 1) as text)||'%' all_math12_0910,
cast(round((all_read04pctprof_0910 * 100), 1) as text)||'%' all_read04_0910, 
cast(round((all_read08pctprof_0910 * 100), 1) as text)||'%' all_read08_0910,
cast(round((all_read10pctprof_0910 * 100), 1) as text)||'%' all_read10_0910, 
cast(round((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
FROM LEA_0910 a
LEFT JOIN LEA_0809 b on leaid09 = leaid08) nt
LEFT JOIN LEA_0708 c on leaid09 = leaid07;