Number, Revenue, Expenses, and Assets of Reporting Public Charities by Subsector, 2002, 2007, 2012

SELECT '2002' AS YEAR, CASE WHEN SUBSTRING(D.NTEEFINAL,1,1)='A' THEN 'a. Arts, culture, and humanities' WHEN SUBSTRING(D.NTEEFINAL,1,2) IN ('B4','B5') THEN 'b1. Higher education' WHEN SUBSTRING(D.NTEEFINAL,1,1)='B' THEN 'b2. Other education' WHEN SUBSTRING(D.NTEEFINAL,1,1) IN ('C','D') THEN 'c. Environment and animals' WHEN SUBSTRING(D.NTEEFINAL,1,3) IN ('E20','E21','E22','E23','E24','F31','E30','E31','E32') THEN 'd1. Hospitals and primary care facilities' WHEN SUBSTRING(D.NTEEFINAL,1,1) IN ('E','F','G','H') THEN 'd2. Other health care' WHEN SUBSTRING(D.NTEEFINAL,1,1) IN ('I','J','K','L','M','N','O','P') THEN 'e. Human services' WHEN SUBSTRING(D.NTEEFINAL,1,1)='Q' THEN 'f. International' WHEN SUBSTRING(D.NTEEFINAL,1,1) IN ('R','S','T','U','V','W','Y','Z') THEN 'g. Public and social benefit' WHEN SUBSTRING(D.NTEEFINAL,1,1)='X' THEN 'h. Religious related' ELSE 'ERROR' END AS CAT, COUNT(A.EIN), SUM(TOTREV) AS Revenue, SUM(EXPS) as Expenses, SUM(ASS_EOY) as Assets_EOY FROM nccs.core2002pc A LEFT JOIN nccs.nteedocAllEins D ON A.EIN=D.EIN WHERE (A.OUTNCCS IS NULL OR A.OUTNCCS <> 'OUT') AND A.FNDNCD NOT IN ('02','03','04') AND GRREC >=25000 AND A.fips='12009' GROUP BY CAT UNION SELECT '2007' AS YEAR, CASE WHEN SUBSTRING(D.NTEEFINAL,1,1)='A' THEN 'a. Arts, culture, and humanities' WHEN SUBSTRING(D.NTEEFINAL,1,2) IN ('B4','B5') THEN 'b1. Higher education' WHEN SUBSTRING(D.NTEEFINAL,1,1)='B' THEN 'b2. Other education' WHEN SUBSTRING(D.NTEEFINAL,1,1) IN ('C','D') THEN 'c. Environment and animals' WHEN SUBSTRING(D.NTEEFINAL,1,3) IN ('E20','E21','E22','E23','E24','F31','E30','E31','E32') THEN 'd1. Hospitals and primary care facilities' WHEN SUBSTRING(D.NTEEFINAL,1,1) IN ('E','F','G','H') THEN 'd2. Other health care' WHEN SUBSTRING(D.NTEEFINAL,1,1) IN ('I','J','K','L','M','N','O','P') THEN 'e. Human services' WHEN SUBSTRING(D.NTEEFINAL,1,1)='Q' THEN 'f. International' WHEN SUBSTRING(D.NTEEFINAL,1,1) IN ('R','S','T','U','V','W','Y','Z') THEN 'g. Public and social benefit' WHEN SUBSTRING(D.NTEEFINAL,1,1)='X' THEN 'h. Religious related' ELSE 'ERROR' END AS CAT, COUNT(A.EIN), SUM(TOTREV) AS Revenue, SUM(EXPS) as Expenses, SUM(ASS_EOY) as Assets_EOY FROM nccs.core2007pc A LEFT JOIN nccs.nteedocAllEins D ON A.EIN=D.EIN WHERE (A.OUTNCCS IS NULL OR A.OUTNCCS <> 'OUT') AND A.FNDNCD NOT IN ('02','03','04') AND GRREC >=25000 AND A.fips='12009' GROUP BY CAT UNION SELECT '2012' AS YEAR, CASE WHEN SUBSTRING(D.NTEEFINAL,1,1)='A' THEN 'a. Arts, culture, and humanities' WHEN SUBSTRING(D.NTEEFINAL,1,2) IN ('B4','B5') THEN 'b1. Higher education' WHEN SUBSTRING(D.NTEEFINAL,1,1)='B' THEN 'b2. Other education' WHEN SUBSTRING(D.NTEEFINAL,1,1) IN ('C','D') THEN 'c. Environment and animals' WHEN SUBSTRING(D.NTEEFINAL,1,3) IN ('E20','E21','E22','E23','E24','F31','E30','E31','E32') THEN 'd1. Hospitals and primary care facilities' WHEN SUBSTRING(D.NTEEFINAL,1,1) IN ('E','F','G','H') THEN 'd2. Other health care' WHEN SUBSTRING(D.NTEEFINAL,1,1) IN ('I','J','K','L','M','N','O','P') THEN 'e. Human services' WHEN SUBSTRING(D.NTEEFINAL,1,1)='Q' THEN 'f. International' WHEN SUBSTRING(D.NTEEFINAL,1,1) IN ('R','S','T','U','V','W','Y','Z') THEN 'g. Public and social benefit' WHEN SUBSTRING(D.NTEEFINAL,1,1)='X' THEN 'h. Religious related' ELSE 'ERROR' END AS CAT, COUNT(A.EIN), SUM(TOTREV) AS Revenue, SUM(EXPS) as Expenses, SUM(ASS_EOY) as Assets_EOY FROM nccs.core2012pc A LEFT JOIN nccs.nteedocAllEins D ON A.EIN=D.EIN WHERE (A.OUTNCCS IS NULL OR A.OUTNCCS <> 'OUT') AND A.FNDNCD NOT IN ('02','03','04') AND GRREC >=50000 AND A.fips='12009' GROUP BY CAT