Registered public charities, 2004, 2009, 2014, and Latest Year (10/2020)

select '2004' as fileyear, case when b.NteeFinal like 'A%' THEN 'Arts' WHEN b.NteeFinal LIKE 'B%' THEN 'Education' WHEN SUBSTRING(b.NteeFinal,1,1) IN ('C','D') THEN 'Environment & animals' WHEN SUBSTRING(b.NteeFinal,1,1) BETWEEN 'E' AND 'H' THEN 'Health care' WHEN SUBSTRING(b.NteeFinal,1,1) BETWEEN 'I' AND 'P' THEN 'Human services' ELSE 'Other' END as NteeCode, count(*) as Number_of_Public_Charities from bmf.bm0404c3 a INNER JOIN nccs.nteedocAllEins b ON a.ein= b.ein AND IFNULL(b.LatestBMF,0)<>-1 where a.fips='12009' and a.fndncd not in ('02','03','04') group by fileyear, NTEEcode UNION select '2009' as fileyear, case when b.NteeFinal like 'A%' THEN 'Arts' WHEN b.NteeFinal LIKE 'B%' THEN 'Education' WHEN SUBSTRING(b.NteeFinal,1,1) IN ('C','D') THEN 'Environment & animals' WHEN SUBSTRING(b.NteeFinal,1,1) BETWEEN 'E' AND 'H' THEN 'Health care' WHEN SUBSTRING(b.NteeFinal,1,1) BETWEEN 'I' AND 'P' THEN 'Human services' ELSE 'Other' END as NteeCode, count(*) as Number_of_Public_Charities FROM bmf.bm0910c3 a INNER JOIN nccs.nteedocAllEins b ON a.ein = b.ein AND IFNULL(b.LatestBMF,0)<>-1 where a.fips='12009' and a.fndncd not in ('02','03','04') group by fileyear, NTEEcode UNION select '2014' as fileyear, case when b.NteeFinal like 'A%' THEN 'Arts' WHEN b.NteeFinal LIKE 'B%' THEN 'Education' WHEN SUBSTRING(b.NteeFinal,1,1) IN ('C','D') THEN 'Environment & animals' WHEN SUBSTRING(b.NteeFinal,1,1) BETWEEN 'E' AND 'H' THEN 'Health care' WHEN SUBSTRING(b.NteeFinal,1,1) BETWEEN 'I' AND 'P' THEN 'Human services' ELSE 'Other' END as NteeCode, count(*) as Number_of_Public_Charities FROM bmf.bm1406c3 a INNER JOIN nccs.nteedocAllEins b ON a.ein = b.ein AND IFNULL(b.LatestBMF,0)<>-1 where a.fips='12009' and a.fndncd not in ('02','03','04') group by fileyear, NTEEcode UNION select '2020 (October)' as fileyear, case when b.NteeFinal like 'A%' THEN 'Arts' WHEN b.NteeFinal LIKE 'B%' THEN 'Education' WHEN SUBSTRING(b.NteeFinal,1,1) IN ('C','D') THEN 'Environment & animals' WHEN SUBSTRING(b.NteeFinal,1,1) BETWEEN 'E' AND 'H' THEN 'Health care' WHEN SUBSTRING(b.NteeFinal,1,1) BETWEEN 'I' AND 'P' THEN 'Human services' ELSE 'Other' END as NteeCode, count(*) as Number_of_Public_Charities FROM bmf.bm2010c3 a INNER JOIN nccs.nteedocAllEins b ON a.ein = b.ein AND IFNULL(b.LatestBMF,0)<>-1 where a.fips='12009' and a.fndncd not in ('02','03','04') group by fileyear, NTEEcode;