clean-sch.sql

CREATE TABLE clean_lea AS 
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_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_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_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_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_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_read12pctprof_0809 * 100), 1) as text)||'%' all_read12_0809
FROM LEA_0910 a
LEFT JOIN LEA_0809 b on leaid09 = leaid08
UNION ALL
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_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_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_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_read12pctprof_0809 * 100), 1) as text)||'%' all_read12_0809
FROM LEA_0809 b
LEFT JOIN LEA_0910 a on leaid08 = leaid09
WHERE a.leaid IS NULL
) nt
LEFT JOIN LEA_0708 c on leaid09 = leaid07
UNION ALL
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_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_read12pctprof_0708 * 100), 1) as text)||'%' all_read12_0708
FROM LEA_0708 c
LEFT JOIN 
(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_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_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_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_read12pctprof_0809 * 100), 1) as text)||'%' all_read12_0809
FROM LEA_0910 a
LEFT JOIN LEA_0809 b on leaid09 = leaid08
UNION ALL
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_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_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_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_read12pctprof_0809 * 100), 1) as text)||'%' all_read12_0809
FROM LEA_0809 b
LEFT JOIN LEA_0910 a on leaid08 = leaid09
WHERE a.leaid IS NULL
) nt on leaid07 = leaid09
WHERE leaid09 IS NULL;