/* **************************************************************************************** */
/* Paper: The Economic Consequences of Financial Audit Regulation in the Charitable Sector
   
   Author: Raphael Duguay
   
   Refer to "0- Readme.pdf" for detailed instructions 
      
   Table of Contents: 
       Section 1: Cleansing raw data
	   Section 2: Charity-year sample 
       Section 3: Variables and tables for analyses
*/
/* **************************************************************************************** */


/* **************************************************************************************** */
/* Section 1: Cleansing raw data                                                            */
/* **************************************************************************************** */

/* **************************************************************************************** */
/* Cleansing datasource: NCCS Core */	

-- Retrieve relevant variables
SELECT 				ROW_NO = ROW_NUMBER() OVER (ORDER BY SOURCE_FILE, EIN, TAXPER)  ,
					FISCAL_YEAR      = CONVERT(INT,SUBSTRING(TAXPER,1,4))           ,
					PERIOD_END       = CONVERT(DATE,SUBSTRING(TAXPER,1,6)+'01',112) ,
					EIN              = SUBSTRING(EIN,1,2)+'-'+SUBSTRING(EIN,3,7)    ,
					ORGNAME          = NAME                                         ,
					STATE                                                           ,
					CITY                                                            ,
					NTEE_CODE        = SUBSTRING(NTEEFINAL,1,3)                     ,
					CONTRIB_ALL      = CONT                                         ,
					REVENUE_TOTAL    = TOTREV                                       ,
					ASSETS_TOTAL_EOY = ASS_EOY                                      ,
					GROSS_INC		 = GRREC	                                    ,
					FIPS             = CASE WHEN LEN(FIPS)=5 THEN FIPS ELSE NULL END,
					RULEDATE         = TRY_CONVERT(INT,SUBSTRING(RULEDATE,1,4)) 
INTO				NCCS_CORE_PC_CLEANSED01					
FROM				JMP_NCCS_CORE_PC.dbo.CORE_PC 
WHERE				CONVERT(INT,SUBSTRING(TAXPER,1,4)) >= 1998
;

-- Retain only one observation per charity-year combination (e.g., in some rare cases charities amend their Form 990)
SELECT					A.*
INTO					NCCS_CORE_PC_CLEANSED
FROM					NCCS_CORE_PC_CLEANSED01 AS A
					INNER JOIN
						(					
						SELECT		EIN                 ,
									FISCAL_YEAR         ,
									ROW_NO = MAX(ROW_NO) 
						FROM		NCCS_CORE_PC_CLEANSED01
						GROUP BY	EIN        ,
									FISCAL_YEAR		
						) AS B	
					ON	A.ROW_NO = B.ROW_NO
;	

-- Drop intermediary table 
DROP TABLE NCCS_CORE_PC_CLEANSED01;

/* **************************************************************************************** */
/* Cleansing datasource: NCCS BMF */	

-- Retrieve relevant variables
SELECT 				ROW_NO = ROW_NUMBER() OVER (ORDER BY SOURCE_FILE, EIN, CTAXPER),
					FISCAL_YEAR    = CONVERT(INT,SUBSTRING(CTAXPER,1,4))           ,
					PERIOD_END     = CONVERT(DATE,SUBSTRING(CTAXPER,1,6)+'01',112) ,
					EIN            = SUBSTRING(EIN,1,2)+'-'+SUBSTRING(EIN,3,7)     ,
					ORGNAME        = NAME                                          ,
					STATE                                                          ,
					CITY                                                           ,
					LEVEL1                                                         ,
					FIPS           = CASE WHEN LEN(FIPS)=5 THEN FIPS ELSE NULL END ,
					RULEDATE       = TRY_CONVERT(INT,SUBSTRING(RULEDATE,1,4))      ,
					NTEE_CODE      = SUBSTRING(NTEECC,1,3)
INTO				NCCS_BMF_CLEANSED01
FROM				JMP_NCCS_BMF.dbo.NCCS_BMF 
WHERE				CONVERT(INT,SUBSTRING(CTAXPER,1,4)) >= 1998		
;

-- Retain only one observation per charity-year combination 
SELECT					A.*
INTO					NCCS_BMF_CLEANSED
FROM					NCCS_BMF_CLEANSED01 AS A
					INNER JOIN
						(					
						SELECT		EIN                 ,
									FISCAL_YEAR         ,
									ROW_NO = MAX(ROW_NO)  
						FROM		NCCS_BMF_CLEANSED01
						GROUP BY	EIN        ,
									FISCAL_YEAR		
						) AS B	
					ON	A.ROW_NO = B.ROW_NO
;	

-- Drop intermediary table 
DROP TABLE NCCS_BMF_CLEANSED01;

/* **************************************************************************************** */
/* Cleansing datasource: NCCS-Guidestar */	

-- Retrieve relevant variables
SELECT						RD_ROW_NO              = ROW_NUMBER() OVER (ORDER BY A.EIN)                   ,
							RD_DATASOURCE          = 'NCCS_GUIDESTAR'                                     ,
							EIN                    = SUBSTRING(A.EIN, 1, 2) + '-' + SUBSTRING(A.EIN, 3, 7),
							FISCAL_YEAR            = CONVERT(INT,DATEPART(YEAR, A.YR_ENDIN))              ,
							PERIOD_END             = A.yr_endin                                           ,
							ORGNAME                = A.name                                               ,
							A.STATE                                                                       ,
							A.CITY                                                                        ,
							NTEE_CODE              = SUBSTRING(A.nteecc,1,3)                              ,
							A.FIPS                                                                        ,
							GROUP_FILING           = CASE WHEN A.p0grprtn = 'Y' THEN 1 ELSE 0 END         ,
							ASSETS_TOTAL_EOY       = C.p4e_Asst		                                      ,
							REVENUE_TOTAL          = D.p1totRev                                           ,
							CONTRIB_ALL            = D.p1tCont                                            ,
							GOV_GRANTS             = d.p1govGt                                            ,
							FED_CAMPAIGNS          = d.p1indsup                                           ,
							PPE_INVEST_NET         = C.p4e_iLnd                                           ,
							PPE_NET                = C.p4e_Land                                           ,
							ACCOUNTING_FEES_TOTAL  = B.p2tacfee                                           ,
							TOTAL_PROGRAM_SERVICES = B.p2pTot                                             ,
							EXPENSES_TOTAL         = B.p2tTot                                             ,
							GROSS_INC              = ISNULL(D.p1totRev,0)+ABS(ISNULL(D.p1rentE,0))+ABS(ISNULL(D.p1securC,0))+ABS(ISNULL(D.p1othC,0))+ABS(ISNULL(D.p1spEvE,0))+ABS(ISNULL(D.p1invntC,0))
INTO						NCCS_GUIDESTAR_CLEANSED1						
FROM						JMP_NCCS_Guidestar.dbo.Master_Header AS A
						LEFT OUTER JOIN
							JMP_NCCS_Guidestar.dbo.Functional_Expenses AS B
						ON	A.nccskey = B.nccskey
						LEFT OUTER JOIN
							JMP_NCCS_Guidestar.dbo.Balance_Sheet AS C
						ON	A.nccskey = C.nccskey
						LEFT OUTER JOIN
							JMP_NCCS_Guidestar.dbo.Revenue_and_Expenses AS D
						ON	A.nccskey = D.nccskey
						LEFT OUTER JOIN
							JMP_NCCS_Guidestar.dbo.Other_Info AS E	
						ON	A.nccskey = E.nccskey				
;		

-- Retain only one observation per charity-year combination (e.g., in some rare cases charities amend their Form 990)
SELECT					A.*
INTO					NCCS_GUIDESTAR_CLEANSED 
FROM					NCCS_GUIDESTAR_CLEANSED1 AS A
					INNER JOIN
						(					
						SELECT		EIN                       ,
									FISCAL_YEAR               ,
									RD_ROW_NO = MAX(RD_ROW_NO) 
						FROM		NCCS_GUIDESTAR_CLEANSED1
						GROUP BY	EIN        ,
									FISCAL_YEAR		
						) AS B	
					ON	A.RD_ROW_NO = B.RD_ROW_NO
;

-- Drop intermediary table
DROP TABLE NCCS_GUIDESTAR_CLEANSED1;

/* **************************************************************************************** */
/* Cleansing datasource: Guidestar_0408 */	

-- Retrieve relevant variables
SELECT						RD_ROW_NO               = ROW_NUMBER() OVER (ORDER BY A.FIN_ID)            ,
							RD_DATASOURCE           = 'GUIDESTAR_0408'                                 ,
							A.EIN                                                                      ,
							FISCAL_YEAR             = DATEPART(YEAR,A.tax_year_ending)                 ,
							PERIOD_END              = A.tax_year_ending                                ,
							GROUP_FILING            = CASE WHEN H.group_return_yn = 1 THEN 1 ELSE 0 END,
							ORGNAME                 = A.ORG_NAME                                       ,
							STATE                   = E.STATE_CODE                                     ,
							E.CITY                                                                     ,
							NTEE_CODE               = SUBSTRING(F.NTEE_CODE,1,3)                       ,
							C.ASSETS_TOTAL_EOY                                                         ,
							D.REVENUE_TOTAL                                                            ,
							CONTRIB_ALL             = D.contributions_total                            ,
							GOV_GRANTS              = D.government_grants                              ,
							FED_CAMPAIGNS           = D.indirect_support                               ,
							PPE_INVEST_NET          = C.invest_lbe_eoy                                 ,
							PPE_NET                 = C.lbe_eoy                                        ,
							B.ACCOUNTING_FEES_TOTAL                                                    ,
							TOTAL_PROGRAM_SERVICES  = B.funct_expenses_services                        ,
							EXPENSES_TOTAL          = B.funct_expenses_total                           ,
							GROSS_INC               = ISNULL(D.contributions_total,0)+ABS(ISNULL(D.rental_expenses,0))+ABS(ISNULL(D.cost_securities,0))+ABS(ISNULL(D.cost_other,0))+ABS(ISNULL(D.direct_expenses,0))+ABS(ISNULL(D.cost_goods,0))
INTO						GUIDESTAR_0408_CLEANSED_1							
FROM						JMP_Guidestar_20032008.dbo.f990_header AS A
						LEFT OUTER JOIN
							JMP_Guidestar_20032008.dbo.f990_funct_exps AS B
						ON	A.FIN_ID = B.FIN_ID
						LEFT OUTER JOIN
							JMP_Guidestar_20032008.dbo.f990_balance_sheets AS C
						ON	A.FIN_ID = C.FIN_ID
						LEFT OUTER JOIN
							JMP_Guidestar_20032008.dbo.f990_rev_exp AS D
						ON	A.FIN_ID = D.FIN_ID	
						LEFT OUTER JOIN
							JMP_Guidestar_20032008.dbo.addresses AS E
						ON	A.FIN_ID = E.FIN_ID	
						LEFT OUTER JOIN						
							JMP_Guidestar_20032008.dbo.BMF AS F
						ON	A.EIN = F.EIN	
						LEFT OUTER JOIN						
							JMP_Guidestar_20032008.dbo.f990_other_info AS G
						ON	A.FIN_ID = G.FIN_ID		
						LEFT OUTER JOIN						
							JMP_Guidestar_20032008.dbo.f990_header_supp AS H
						ON	A.FIN_ID = H.FIN_ID					
;

-- Retain only one observation per charity-year combination (e.g., in some rare cases charities amend their Form 990)
SELECT					A.*
INTO					GUIDESTAR_0408_CLEANSED 
FROM					GUIDESTAR_0408_CLEANSED_1 AS A
					INNER JOIN
						(					
						SELECT		EIN                       ,
									FISCAL_YEAR               ,
									RD_ROW_NO = MAX(RD_ROW_NO)
						FROM		GUIDESTAR_0408_CLEANSED_1
						GROUP BY	EIN,
									FISCAL_YEAR		
						) AS B	
					ON	A.RD_ROW_NO = B.RD_ROW_NO
;					

-- Replace missing numbers with 0. (On the Form 990, leaving a line blank is equivalent to indicating "0". Guidestar records a "null" value for fields left blank)
UPDATE GUIDESTAR_0408_CLEANSED SET REVENUE_TOTAL			= 0 WHERE REVENUE_TOTAL					IS NULL;
UPDATE GUIDESTAR_0408_CLEANSED SET CONTRIB_ALL				= 0 WHERE CONTRIB_ALL					IS NULL;
UPDATE GUIDESTAR_0408_CLEANSED SET GOV_GRANTS				= 0 WHERE GOV_GRANTS					IS NULL;
UPDATE GUIDESTAR_0408_CLEANSED SET FED_CAMPAIGNS			= 0 WHERE FED_CAMPAIGNS					IS NULL;
UPDATE GUIDESTAR_0408_CLEANSED SET ASSETS_TOTAL_EOY			= 0 WHERE ASSETS_TOTAL_EOY				IS NULL;
UPDATE GUIDESTAR_0408_CLEANSED SET ACCOUNTING_FEES_TOTAL	= 0 WHERE ACCOUNTING_FEES_TOTAL			IS NULL;
UPDATE GUIDESTAR_0408_CLEANSED SET TOTAL_PROGRAM_SERVICES	= 0 WHERE TOTAL_PROGRAM_SERVICES		IS NULL;
UPDATE GUIDESTAR_0408_CLEANSED SET EXPENSES_TOTAL			= 0 WHERE EXPENSES_TOTAL				IS NULL;

-- Drop intermediary table 
DROP TABLE GUIDESTAR_0408_CLEANSED_1;

/* **************************************************************************************** */
/* Cleansing datasource: Guidestar_post2008 */	

-- Retrieve relevant variables
SELECT						RD_ROW_NO                 = ROW_NUMBER() OVER (ORDER BY A.XML_DOCUMENT_ID)                                 ,
							RD_DATASOURCE             = 'GUIDESTAR_POST2008'                                                           ,
							FISCAL_YEAR               = DATEPART(YEAR,A.PERIOD_END)                                                    ,
							A.PERIOD_END                                                                                               ,
							A.EIN                                                                                                      ,
							A.ORGNAME                                                                                                  ,
							A.FORMATION_YEAR                                                                                           ,
							A.STATE                                                                                                    ,
							A.CITY                                                                                                     ,
							GROUP_FILING              = CASE WHEN A.GROUP_RET_STATUS_CBX = 'Y' THEN 1 ELSE 0 END                       ,
							NTEE_CODE                 = SUBSTRING(BMF.NTEE_CODE,1,3)                                                   ,
							VIII.CONTRIB_ALL                                                                                           ,
							VIII.REVENUE_TOTAL                                                                                         ,
							VIII.GOV_GRANTS                                                                                            ,
							VIII.FED_CAMPAIGNS                                                                                         ,
							X.ASSETS_TOTAL_EOY                                                                                         ,
							AUDIT                     = CASE WHEN IV.AFS_GAAP_CBX = 'Y' OR XII.FINS_AUDITED_CBX = 'Y' THEN 1 ELSE 0 END,											
							ASSET_DIVISION_CBX        = CASE WHEN VI.ASSET_DIVISION_CBX = 'Y' THEN 1 ELSE 0 END		                   ,
							SCHED_B                   = CASE WHEN IV.RQ_SCHEDB_CBX      = 'Y' THEN 1 ELSE 0 END                        ,
							PPE_NET                   = X.LBE_EOY                                                                      ,
							POST990                   = CASE WHEN VI.OWN_WEBSITE_CBX    = 'Y' THEN 1 ELSE 0 END                        ,
							IX.ACCOUNTING_FEES_TOTAL                                                                                   ,
							IX.TOTAL_PROGRAM_SERVICES                                                                                  ,
							IX.EXPENSES_TOTAL                                                                                          ,
							SCHED_O_III               = CASE WHEN III.SCH_O_III_CBX     = 'Y' THEN 1 ELSE 0 END                        ,
							GROSS_INC                 = ISNULL(VIII.REVENUE_TOTAL,0)+ABS(ISNULL(VIII.RENT_EXP_REAL,0))+ABS(ISNULL(VIII.RENT_EXP_PERSONAL,0))+ABS(ISNULL(VIII.COST_SECURITIES,0))+ABS(ISNULL(VIII.COST_OTHER,0))+ABS(ISNULL(VIII.DIRECT_EXPENSES,0))+ABS(ISNULL(VIII.GAMING_EXPENSES,0))+ABS(ISNULL(VIII.COST_GOODS,0))
INTO						GUIDESTAR_POST2008_CLEANSED_1						
FROM						JMP_Guidestar_Post2008.dbo.f990_header AS A
						
						LEFT OUTER JOIN
							(
							SELECT		EIN      ,
										NTEE_CODE
							FROM		JMP_Guidestar_Post2008.dbo.BMF
							) AS BMF
						ON	A.EIN	= BMF.EIN
		
						LEFT OUTER JOIN
							JMP_Guidestar_Post2008.dbo.f990_Part_I AS I
						ON	A.XML_DOCUMENT_ID = I.XML_DOCUMENT_ID
		
						LEFT OUTER JOIN
							JMP_Guidestar_Post2008.dbo.F990_PART_III AS III
						ON	A.XML_DOCUMENT_ID = III.XML_DOCUMENT_ID
						
						LEFT OUTER JOIN
							JMP_Guidestar_Post2008.dbo.F990_PART_IV AS IV
						ON	A.XML_DOCUMENT_ID = IV.XML_DOCUMENT_ID

						LEFT OUTER JOIN
							JMP_Guidestar_Post2008.dbo.F990_PART_VI AS VI
						ON	A.XML_DOCUMENT_ID = VI.XML_DOCUMENT_ID

						LEFT OUTER JOIN
							JMP_Guidestar_Post2008.dbo.F990_PART_VIII AS VIII
						ON	A.XML_DOCUMENT_ID = VIII.XML_DOCUMENT_ID	

						LEFT OUTER JOIN
							JMP_Guidestar_Post2008.dbo.F990_PART_IX AS IX
						ON	A.XML_DOCUMENT_ID = IX.XML_DOCUMENT_ID		

						LEFT OUTER JOIN
							JMP_Guidestar_Post2008.dbo.F990_PART_X AS X
						ON	A.XML_DOCUMENT_ID = X.XML_DOCUMENT_ID			
						
						LEFT OUTER JOIN
							JMP_Guidestar_Post2008.dbo.F990_PART_XII AS XII
						ON	A.XML_DOCUMENT_ID = XII.XML_DOCUMENT_ID			
;			

-- Retain only one observation per charity-year combination (e.g., in some rare cases charities amend their Form 990)
SELECT					A.*
INTO					GUIDESTAR_POST2008_CLEANSED 
FROM					GUIDESTAR_POST2008_CLEANSED_1 AS A
					INNER JOIN
						(					
						SELECT		EIN,
									FISCAL_YEAR,
									RD_ROW_NO = MAX(RD_ROW_NO)
						FROM		GUIDESTAR_POST2008_CLEANSED_1
						GROUP BY	EIN,
									FISCAL_YEAR		
						) AS B	
					ON	A.RD_ROW_NO = B.RD_ROW_NO
;					

-- Replace missing numbers with 0. (On the Form 990, leaving a line blank is equivalent to indicating "0". Guidestar records a "null" value for fields left blank)
UPDATE GUIDESTAR_POST2008_CLEANSED SET CONTRIB_ALL				= 0 WHERE CONTRIB_ALL					IS NULL;
UPDATE GUIDESTAR_POST2008_CLEANSED SET REVENUE_TOTAL			= 0 WHERE REVENUE_TOTAL					IS NULL;
UPDATE GUIDESTAR_POST2008_CLEANSED SET ASSETS_TOTAL_EOY			= 0 WHERE ASSETS_TOTAL_EOY				IS NULL;
UPDATE GUIDESTAR_POST2008_CLEANSED SET GOV_GRANTS				= 0 WHERE GOV_GRANTS					IS NULL;
UPDATE GUIDESTAR_POST2008_CLEANSED SET PPE_NET					= 0 WHERE PPE_NET						IS NULL;
UPDATE GUIDESTAR_POST2008_CLEANSED SET ACCOUNTING_FEES_TOTAL	= 0 WHERE ACCOUNTING_FEES_TOTAL			IS NULL;
UPDATE GUIDESTAR_POST2008_CLEANSED SET TOTAL_PROGRAM_SERVICES	= 0 WHERE TOTAL_PROGRAM_SERVICES		IS NULL;
UPDATE GUIDESTAR_POST2008_CLEANSED SET EXPENSES_TOTAL			= 0 WHERE EXPENSES_TOTAL				IS NULL;
UPDATE GUIDESTAR_POST2008_CLEANSED SET FED_CAMPAIGNS			= 0 WHERE FED_CAMPAIGNS					IS NULL;

-- Drop intermediary table 
DROP TABLE GUIDESTAR_POST2008_CLEANSED_1;

/* **************************************************************************************** */
/* Cleansing datasource: Guidestar_EZ */	

-- Retrieve relevant variables
SELECT						RD_ROW_NO              = ROW_NUMBER() OVER (ORDER BY A.XML_DOCUMENT_ID)                                                                    ,
							FISCAL_YEAR            = DATEPART(YEAR,A.PERIOD_END)                                                                                       ,
							A.PERIOD_END                                                                                                                               ,
							A.EIN                                                                                                                                      ,
							A.ORGNAME                                                                                                                                  ,
							A.STATE                                                                                                                                    ,
							A.CITY                                                                                                                                     ,
							NTEE_CODE              = SUBSTRING(E.NTEE_CODE,1,3)                                                                                        ,
							CONTRIB_ALL            = B.CONTRIB_TOT                                                                                                     ,
							B.REVENUE_TOTAL                                                                                                                            ,
							C.ASSETS_TOTAL_EOY                                                                                                                         ,
							OTHER_STATES           = F.STATES_FILED_TEXT                                                                                               ,
							TOTAL_PROGRAM_SERVICES = G.EXPENSES_TOTAL_32                                                                                               ,
							EXPENSES_TOTAL         = B.FUNCT_EXPENSES_TOTAL                                                                                            ,
							GROSS_INC              = ISNULL(B.REVENUE_TOTAL,0)+ABS(ISNULL(B.COST_OTHER,0))+ABS(ISNULL(B.DIRECT_EXPENSES,0))+ABS(ISNULL(B.COST_GOODS,0))
INTO						GUIDESTAR_EZ_CLEANSED1							
FROM						JMP_Guidestar_990EZ.dbo.F990EZ_Header AS A
						INNER JOIN
							JMP_Guidestar_990EZ.dbo.F990EZ_Part_I AS B
						ON	A.XML_DOCUMENT_ID = B.XML_DOCUMENT_ID
						INNER JOIN
							JMP_Guidestar_990EZ.dbo.F990EZ_Part_II AS C
						ON	A.XML_DOCUMENT_ID = C.XML_DOCUMENT_ID
						INNER JOIN
							(
							SELECT DISTINCT	EIN,
											NTEE_CODE
							FROM			JMP_Guidestar_990EZ.dbo.BMF
							)			 AS E
						ON	A.EIN = E.EIN
						INNER JOIN
							JMP_Guidestar_990EZ.dbo.F990EZ_Part_V AS F
						ON	A.XML_DOCUMENT_ID = F.XML_DOCUMENT_ID
						INNER JOIN
							JMP_Guidestar_990EZ.dbo.F990EZ_Part_III AS G
						ON	A.XML_DOCUMENT_ID = G.XML_DOCUMENT_ID
;						

-- Retain only one observation per charity-year combination (e.g., in some rare cases charities amend their Form 990)
SELECT					A.*
INTO					GUIDESTAR_EZ_CLEANSED 
FROM					GUIDESTAR_EZ_CLEANSED1 AS A
					INNER JOIN
						(					
						SELECT		EIN                       ,
									FISCAL_YEAR               ,
									RD_ROW_NO = MAX(RD_ROW_NO)  
						FROM		GUIDESTAR_EZ_CLEANSED1
						GROUP BY	EIN,
									FISCAL_YEAR		
						) AS B	
					ON	A.RD_ROW_NO = B.RD_ROW_NO
;					

-- Replace missing numbers with 0. (On the Form 990, leaving a line blank is equivalent to indicating "0". Guidestar records a "null" value for fields left blank)
UPDATE GUIDESTAR_EZ_CLEANSED SET REVENUE_TOTAL			= 0 WHERE REVENUE_TOTAL					IS NULL;
UPDATE GUIDESTAR_EZ_CLEANSED SET ASSETS_TOTAL_EOY		= 0 WHERE ASSETS_TOTAL_EOY				IS NULL;
UPDATE GUIDESTAR_EZ_CLEANSED SET CONTRIB_ALL			= 0 WHERE CONTRIB_ALL					IS NULL;
UPDATE GUIDESTAR_EZ_CLEANSED SET TOTAL_PROGRAM_SERVICES	= 0 WHERE TOTAL_PROGRAM_SERVICES		IS NULL;
UPDATE GUIDESTAR_EZ_CLEANSED SET EXPENSES_TOTAL			= 0 WHERE EXPENSES_TOTAL				IS NULL;

-- Drop invalid states
DELETE FROM GUIDESTAR_EZ_CLEANSED WHERE LEN(STATE) > 3;		

-- Drop intermediary table 
DROP TABLE GUIDESTAR_EZ_CLEANSED1;

/* **************************************************************************************** */
/* Cleansing IRS state-level data on tax payers (aka statistics of income data)*/	

/* Format data for years 1997-2001 */
-- Unpivot 
SELECT				FISCAL_YEAR = YEAR,
					STATE             ,
					ITEM              ,	
					AGI_SIZE          ,
					VALUE		
INTO				TEMP_SOI_STATE_1997_2001_1					
FROM				JMP_IRS_SOI_ITEMIZED_STATE.dbo.STATE_1997_2001
UNPIVOT
(
	VALUE FOR AGI_SIZE IN ([All_returns], [0000K_AND_UNDER], [0001K_TO_0010K], [0010K_TO_0020K], [0020K_TO_0030K], [0030K_TO_0050K], [0050K_TO_0075K], [0075K_TO_0100K], [0100K_TO_0150K], [0150K_TO_0200K], [0200K_TO_0500K], [0500K_TO_1000K], [1000K_AND_ABOVE])
) U
;	

-- Drop observations where adjusted gross income = "all returns" to avoid double counting  
DELETE FROM TEMP_SOI_STATE_1997_2001_1 WHERE AGI_SIZE = 'All_returns';

-- Set data type for [value] as float 
ALTER TABLE TEMP_SOI_STATE_1997_2001_1 ALTER COLUMN VALUE FLOAT;

-- Pivot 
SELECT				*
INTO				TEMP_SOI_STATE_1997_2001_2
FROM				TEMP_SOI_STATE_1997_2001_1
PIVOT
(
SUM(VALUE) FOR ITEM IN ([Nb_returns], [Nb_joint_returns], [AGI], [Itemized_deductions_nb], [Itemized_deductions_amt], [Contributions_nb], [Contributions_amt])
) AS A
;

-- Aggregate by standardized tax brackets (The level of granularity of the tax brackets varies over time in the IRS data. I create standardized brackets that constcuted for all years in my sample)
SELECT				*,
					TAX_BRACKET = 
				CASE
					WHEN 	AGI_SIZE = '0000K_AND_UNDER'
						OR	AGI_SIZE = '0001K_TO_0010K'
						OR	AGI_SIZE = '0010K_TO_0020K'
						OR	AGI_SIZE = '0020K_TO_0030K'
						OR	AGI_SIZE = '0030K_TO_0050K'
					THEN 	'0050K_AND_UNDER'
					
					WHEN 	AGI_SIZE = '0100K_TO_0150K'
						OR	AGI_SIZE = '0150K_TO_0200K'	
					THEN 	'0100K_TO_0200K'				
					
					WHEN	AGI_SIZE = '0200K_TO_0500K'
						OR	AGI_SIZE = '0500K_TO_1000K'
						OR	AGI_SIZE = '1000K_AND_ABOVE' 
					THEN	'0200K_AND_ABOVE'	
					
					ELSE	AGI_SIZE
				END
INTO				TEMP_SOI_STATE_1997_2001_3
FROM				TEMP_SOI_STATE_1997_2001_2
;

-- Aggregate by state-tax bracket-year
SELECT				FISCAL_YEAR                                           ,
					STATE                                                 ,
					TAX_BRACKET                                           ,
					Nb_returns 				= SUM(Nb_returns)             ,
					Nb_joint_returns		= SUM(Nb_joint_returns)       ,
					AGI                     = SUM(AGI)                    ,
					Itemized_deductions_nb  = SUM(Itemized_deductions_nb) ,
					Itemized_deductions_amt = SUM(Itemized_deductions_amt),	
					Contributions_nb        = SUM(Contributions_nb)       ,
					Contributions_amt       = SUM(Contributions_amt)
INTO				TEMP_SOI_STATE_1997_2001					
FROM				TEMP_SOI_STATE_1997_2001_3
GROUP BY			FISCAL_YEAR,
					STATE,
					TAX_BRACKET		
;

/* Format data for years 2002-2003 */
-- Unpivot 
SELECT				FISCAL_YEAR = YEAR,
					STATE             ,
					ITEM              ,	
					AGI_SIZE          ,
					VALUE		
INTO				TEMP_SOI_STATE_2002_2003_1					
FROM				JMP_IRS_SOI_ITEMIZED_STATE.dbo.STATE_2002_2003
UNPIVOT
(
	VALUE FOR AGI_SIZE IN ([All_returns], [0030K_AND_UNDER], [0030K_TO_0050K], [0050K_TO_0075K], [0075K_TO_0100K], [0100K_TO_0200K], [0200K_AND_ABOVE])
) U
;	

-- Drop observations where adjusted gross income = "all returns" to avoid double counting  
DELETE FROM TEMP_SOI_STATE_2002_2003_1 WHERE AGI_SIZE = 'All_returns';

-- Set data type for [value] as float 
ALTER TABLE TEMP_SOI_STATE_2002_2003_1 ALTER COLUMN VALUE FLOAT;

-- Pivot  
SELECT				*
INTO				TEMP_SOI_STATE_2002_2003_2
FROM				TEMP_SOI_STATE_2002_2003_1
PIVOT
(
SUM(VALUE) FOR ITEM IN ([Nb_returns], [Nb_joint_returns], [AGI], [Itemized_deductions_nb], [Itemized_deductions_amt], [Contributions_nb], [Contributions_amt])
) AS A
;

-- Aggregate by standardized tax brackets (The level of granularity of the tax brackets varies over time in the IRS data. I create standardized brackets that constcuted for all years in my sample)
SELECT				*,
					TAX_BRACKET = 
				CASE
					WHEN 	AGI_SIZE = '0030K_AND_UNDER'
						OR	AGI_SIZE = '0030K_TO_0050K'
					THEN 	'0050K_AND_UNDER'
					
					ELSE	AGI_SIZE
				END
INTO				TEMP_SOI_STATE_2002_2003_3
FROM				TEMP_SOI_STATE_2002_2003_2
;

-- Aggregate by state-tax bracket-year
SELECT				FISCAL_YEAR                                           ,
					STATE                                                 ,
					TAX_BRACKET                                           ,
					Nb_returns 				= SUM(Nb_returns)             ,
					Nb_joint_returns		= SUM(Nb_joint_returns)       ,
					AGI                     = SUM(AGI)                    ,
					Itemized_deductions_nb  = SUM(Itemized_deductions_nb) ,
					Itemized_deductions_amt = SUM(Itemized_deductions_amt),	
					Contributions_nb        = SUM(Contributions_nb)       ,
					Contributions_amt       = SUM(Contributions_amt)
INTO				TEMP_SOI_STATE_2002_2003					
FROM				TEMP_SOI_STATE_2002_2003_3
GROUP BY			FISCAL_YEAR,
					STATE,
					TAX_BRACKET		
;

/* Format data for years 2004-2009 */
-- Unpivot 
SELECT				FISCAL_YEAR = YEAR,
					STATE = RIGHT(AGI_SIZE,2),
					ITEM,	
					AGI_SIZE = LEFT(AGI_SIZE, LEN(AGI_SIZE)-3),
					VALUE		
INTO				TEMP_SOI_STATE_2004_2009_1					
FROM				JMP_IRS_SOI_ITEMIZED_STATE.dbo.STATE_2004_2009
UNPIVOT
(
	VALUE FOR AGI_SIZE IN ([All_returns_AL], [0050K_AND_UNDER_AL], [0050K_TO_0075K_AL], [0075K_TO_0100K_AL], [0100K_TO_0200K_AL], [0200K_AND_ABOVE_AL], [All_returns_AK], [0050K_AND_UNDER_AK], [0050K_TO_0075K_AK], [0075K_TO_0100K_AK], [0100K_TO_0200K_AK], [0200K_AND_ABOVE_AK], [All_returns_AZ], [0050K_AND_UNDER_AZ], [0050K_TO_0075K_AZ], [0075K_TO_0100K_AZ], [0100K_TO_0200K_AZ], [0200K_AND_ABOVE_AZ], [All_returns_AR], [0050K_AND_UNDER_AR], [0050K_TO_0075K_AR], [0075K_TO_0100K_AR], [0100K_TO_0200K_AR], [0200K_AND_ABOVE_AR], [All_returns_CA], [0050K_AND_UNDER_CA], [0050K_TO_0075K_CA], [0075K_TO_0100K_CA], [0100K_TO_0200K_CA], [0200K_AND_ABOVE_CA], [All_returns_CO], [0050K_AND_UNDER_CO], [0050K_TO_0075K_CO], [0075K_TO_0100K_CO], [0100K_TO_0200K_CO], [0200K_AND_ABOVE_CO], [All_returns_CT], [0050K_AND_UNDER_CT], [0050K_TO_0075K_CT], [0075K_TO_0100K_CT], [0100K_TO_0200K_CT], [0200K_AND_ABOVE_CT], [All_returns_DE], [0050K_AND_UNDER_DE], [0050K_TO_0075K_DE], [0075K_TO_0100K_DE], [0100K_TO_0200K_DE], [0200K_AND_ABOVE_DE], [All_returns_DC], [0050K_AND_UNDER_DC], [0050K_TO_0075K_DC], [0075K_TO_0100K_DC], [0100K_TO_0200K_DC], [0200K_AND_ABOVE_DC], [All_returns_FL], [0050K_AND_UNDER_FL], [0050K_TO_0075K_FL], [0075K_TO_0100K_FL], [0100K_TO_0200K_FL], [0200K_AND_ABOVE_FL], [All_returns_GA], [0050K_AND_UNDER_GA], [0050K_TO_0075K_GA], [0075K_TO_0100K_GA], [0100K_TO_0200K_GA], [0200K_AND_ABOVE_GA], [All_returns_HI], [0050K_AND_UNDER_HI], [0050K_TO_0075K_HI], [0075K_TO_0100K_HI], [0100K_TO_0200K_HI], [0200K_AND_ABOVE_HI], [All_returns_ID], [0050K_AND_UNDER_ID], [0050K_TO_0075K_ID], [0075K_TO_0100K_ID], [0100K_TO_0200K_ID], [0200K_AND_ABOVE_ID], [All_returns_IL], [0050K_AND_UNDER_IL], [0050K_TO_0075K_IL], [0075K_TO_0100K_IL], [0100K_TO_0200K_IL], [0200K_AND_ABOVE_IL], [All_returns_IN], [0050K_AND_UNDER_IN], [0050K_TO_0075K_IN], [0075K_TO_0100K_IN], [0100K_TO_0200K_IN], [0200K_AND_ABOVE_IN], [All_returns_IA], [0050K_AND_UNDER_IA], [0050K_TO_0075K_IA], [0075K_TO_0100K_IA], [0100K_TO_0200K_IA], [0200K_AND_ABOVE_IA], [All_returns_KS], [0050K_AND_UNDER_KS], [0050K_TO_0075K_KS], [0075K_TO_0100K_KS], [0100K_TO_0200K_KS], [0200K_AND_ABOVE_KS], [All_returns_KY], [0050K_AND_UNDER_KY], [0050K_TO_0075K_KY], [0075K_TO_0100K_KY], [0100K_TO_0200K_KY], [0200K_AND_ABOVE_KY], [All_returns_LA], [0050K_AND_UNDER_LA], [0050K_TO_0075K_LA], [0075K_TO_0100K_LA], [0100K_TO_0200K_LA], [0200K_AND_ABOVE_LA], [All_returns_ME], [0050K_AND_UNDER_ME], [0050K_TO_0075K_ME], [0075K_TO_0100K_ME], [0100K_TO_0200K_ME], [0200K_AND_ABOVE_ME], [All_returns_MD], [0050K_AND_UNDER_MD], [0050K_TO_0075K_MD], [0075K_TO_0100K_MD], [0100K_TO_0200K_MD], [0200K_AND_ABOVE_MD], [All_returns_MA], [0050K_AND_UNDER_MA], [0050K_TO_0075K_MA], [0075K_TO_0100K_MA], [0100K_TO_0200K_MA], [0200K_AND_ABOVE_MA], [All_returns_MI], [0050K_AND_UNDER_MI], [0050K_TO_0075K_MI], [0075K_TO_0100K_MI], [0100K_TO_0200K_MI], [0200K_AND_ABOVE_MI], [All_returns_MN], [0050K_AND_UNDER_MN], [0050K_TO_0075K_MN], [0075K_TO_0100K_MN], [0100K_TO_0200K_MN], [0200K_AND_ABOVE_MN], [All_returns_MS], [0050K_AND_UNDER_MS], [0050K_TO_0075K_MS], [0075K_TO_0100K_MS], [0100K_TO_0200K_MS], [0200K_AND_ABOVE_MS], [All_returns_MO], [0050K_AND_UNDER_MO], [0050K_TO_0075K_MO], [0075K_TO_0100K_MO], [0100K_TO_0200K_MO], [0200K_AND_ABOVE_MO], [All_returns_MT], [0050K_AND_UNDER_MT], [0050K_TO_0075K_MT], [0075K_TO_0100K_MT], [0100K_TO_0200K_MT], [0200K_AND_ABOVE_MT], [All_returns_NE], [0050K_AND_UNDER_NE], [0050K_TO_0075K_NE], [0075K_TO_0100K_NE], [0100K_TO_0200K_NE], [0200K_AND_ABOVE_NE], [All_returns_NV], [0050K_AND_UNDER_NV], [0050K_TO_0075K_NV], [0075K_TO_0100K_NV], [0100K_TO_0200K_NV], [0200K_AND_ABOVE_NV], [All_returns_NH], [0050K_AND_UNDER_NH], [0050K_TO_0075K_NH], [0075K_TO_0100K_NH], [0100K_TO_0200K_NH], [0200K_AND_ABOVE_NH], [All_returns_NJ], [0050K_AND_UNDER_NJ], [0050K_TO_0075K_NJ], [0075K_TO_0100K_NJ], [0100K_TO_0200K_NJ], [0200K_AND_ABOVE_NJ], [All_returns_NM], [0050K_AND_UNDER_NM], [0050K_TO_0075K_NM], [0075K_TO_0100K_NM], [0100K_TO_0200K_NM], [0200K_AND_ABOVE_NM], [All_returns_NY], [0050K_AND_UNDER_NY], [0050K_TO_0075K_NY], [0075K_TO_0100K_NY], [0100K_TO_0200K_NY], [0200K_AND_ABOVE_NY], [All_returns_NC], [0050K_AND_UNDER_NC], [0050K_TO_0075K_NC], [0075K_TO_0100K_NC], [0100K_TO_0200K_NC], [0200K_AND_ABOVE_NC], [All_returns_ND], [0050K_AND_UNDER_ND], [0050K_TO_0075K_ND], [0075K_TO_0100K_ND], [0100K_TO_0200K_ND], [0200K_AND_ABOVE_ND], [All_returns_OH], [0050K_AND_UNDER_OH], [0050K_TO_0075K_OH], [0075K_TO_0100K_OH], [0100K_TO_0200K_OH], [0200K_AND_ABOVE_OH], [All_returns_OK], [0050K_AND_UNDER_OK], [0050K_TO_0075K_OK], [0075K_TO_0100K_OK], [0100K_TO_0200K_OK], [0200K_AND_ABOVE_OK], [All_returns_OR], [0050K_AND_UNDER_OR], [0050K_TO_0075K_OR], [0075K_TO_0100K_OR], [0100K_TO_0200K_OR], [0200K_AND_ABOVE_OR], [All_returns_PA], [0050K_AND_UNDER_PA], [0050K_TO_0075K_PA], [0075K_TO_0100K_PA], [0100K_TO_0200K_PA], [0200K_AND_ABOVE_PA], [All_returns_RI], [0050K_AND_UNDER_RI], [0050K_TO_0075K_RI], [0075K_TO_0100K_RI], [0100K_TO_0200K_RI], [0200K_AND_ABOVE_RI], [All_returns_SC], [0050K_AND_UNDER_SC], [0050K_TO_0075K_SC], [0075K_TO_0100K_SC], [0100K_TO_0200K_SC], [0200K_AND_ABOVE_SC], [All_returns_SD], [0050K_AND_UNDER_SD], [0050K_TO_0075K_SD], [0075K_TO_0100K_SD], [0100K_TO_0200K_SD], [0200K_AND_ABOVE_SD], [All_returns_TN], [0050K_AND_UNDER_TN], [0050K_TO_0075K_TN], [0075K_TO_0100K_TN], [0100K_TO_0200K_TN], [0200K_AND_ABOVE_TN], [All_returns_TX], [0050K_AND_UNDER_TX], [0050K_TO_0075K_TX], [0075K_TO_0100K_TX], [0100K_TO_0200K_TX], [0200K_AND_ABOVE_TX], [All_returns_UT], [0050K_AND_UNDER_UT], [0050K_TO_0075K_UT], [0075K_TO_0100K_UT], [0100K_TO_0200K_UT], [0200K_AND_ABOVE_UT], [All_returns_VT], [0050K_AND_UNDER_VT], [0050K_TO_0075K_VT], [0075K_TO_0100K_VT], [0100K_TO_0200K_VT], [0200K_AND_ABOVE_VT], [All_returns_VA], [0050K_AND_UNDER_VA], [0050K_TO_0075K_VA], [0075K_TO_0100K_VA], [0100K_TO_0200K_VA], [0200K_AND_ABOVE_VA], [All_returns_WA], [0050K_AND_UNDER_WA], [0050K_TO_0075K_WA], [0075K_TO_0100K_WA], [0100K_TO_0200K_WA], [0200K_AND_ABOVE_WA], [All_returns_WV], [0050K_AND_UNDER_WV], [0050K_TO_0075K_WV], [0075K_TO_0100K_WV], [0100K_TO_0200K_WV], [0200K_AND_ABOVE_WV], [All_returns_WI], [0050K_AND_UNDER_WI], [0050K_TO_0075K_WI], [0075K_TO_0100K_WI], [0100K_TO_0200K_WI], [0200K_AND_ABOVE_WI], [All_returns_WY], [0050K_AND_UNDER_WY], [0050K_TO_0075K_WY], [0075K_TO_0100K_WY], [0100K_TO_0200K_WY], [0200K_AND_ABOVE_WY])
) U
;	

-- Drop observations where adjusted gross income = "all returns" to avoid double counting  
DELETE FROM TEMP_SOI_STATE_2004_2009_1 WHERE AGI_SIZE = 'All_returns';

-- Set data type for [value] as float 
ALTER TABLE	TEMP_SOI_STATE_2004_2009_1 ALTER COLUMN VALUE FLOAT;

-- Pivot 
SELECT				*
INTO				TEMP_SOI_STATE_2004_2009
FROM				TEMP_SOI_STATE_2004_2009_1
PIVOT
(
SUM(VALUE) FOR ITEM IN ([Nb_returns], [Nb_joint_returns], [AGI], [Itemized_deductions_nb], [Itemized_deductions_amt], [Contributions_nb], [Contributions_amt])
) AS A
;

/* Format data for years 2010-2011 */
-- Unpivot 
SELECT				FISCAL_YEAR = YEAR,
					STATE = RIGHT(AGI_SIZE,2),
					ITEM,	
					AGI_SIZE = LEFT(AGI_SIZE, LEN(AGI_SIZE)-3),
					VALUE		
INTO				TEMP_SOI_STATE_2010_2011_1					
FROM				JMP_IRS_SOI_ITEMIZED_STATE.dbo.STATE_2010_2011
UNPIVOT
(
	VALUE FOR AGI_SIZE IN ([All_returns_AL], [0001K_AND_UNDER_AL], [0001K_TO_0025K_AL], [0025K_TO_0050K_AL], [0050K_TO_0075K_AL], [0075K_TO_0100K_AL], [0100K_TO_0200K_AL], [0200K_TO_0500K_AL], [0500K_TO_1000K_AL], [1000K_AND_ABOVE_AL], [All_returns_AK], [0001K_AND_UNDER_AK], [0001K_TO_0025K_AK], [0025K_TO_0050K_AK], [0050K_TO_0075K_AK], [0075K_TO_0100K_AK], [0100K_TO_0200K_AK], [0200K_TO_0500K_AK], [0500K_TO_1000K_AK], [1000K_AND_ABOVE_AK], [All_returns_AZ], [0001K_AND_UNDER_AZ], [0001K_TO_0025K_AZ], [0025K_TO_0050K_AZ], [0050K_TO_0075K_AZ], [0075K_TO_0100K_AZ], [0100K_TO_0200K_AZ], [0200K_TO_0500K_AZ], [0500K_TO_1000K_AZ], [1000K_AND_ABOVE_AZ], [All_returns_AR], [0001K_AND_UNDER_AR], [0001K_TO_0025K_AR], [0025K_TO_0050K_AR], [0050K_TO_0075K_AR], [0075K_TO_0100K_AR], [0100K_TO_0200K_AR], [0200K_TO_0500K_AR], [0500K_TO_1000K_AR], [1000K_AND_ABOVE_AR], [All_returns_CA], [0001K_AND_UNDER_CA], [0001K_TO_0025K_CA], [0025K_TO_0050K_CA], [0050K_TO_0075K_CA], [0075K_TO_0100K_CA], [0100K_TO_0200K_CA], [0200K_TO_0500K_CA], [0500K_TO_1000K_CA], [1000K_AND_ABOVE_CA], [All_returns_CO], [0001K_AND_UNDER_CO], [0001K_TO_0025K_CO], [0025K_TO_0050K_CO], [0050K_TO_0075K_CO], [0075K_TO_0100K_CO], [0100K_TO_0200K_CO], [0200K_TO_0500K_CO], [0500K_TO_1000K_CO], [1000K_AND_ABOVE_CO], [All_returns_CT], [0001K_AND_UNDER_CT], [0001K_TO_0025K_CT], [0025K_TO_0050K_CT], [0050K_TO_0075K_CT], [0075K_TO_0100K_CT], [0100K_TO_0200K_CT], [0200K_TO_0500K_CT], [0500K_TO_1000K_CT], [1000K_AND_ABOVE_CT], [All_returns_DE], [0001K_AND_UNDER_DE], [0001K_TO_0025K_DE], [0025K_TO_0050K_DE], [0050K_TO_0075K_DE], [0075K_TO_0100K_DE], [0100K_TO_0200K_DE], [0200K_TO_0500K_DE], [0500K_TO_1000K_DE], [1000K_AND_ABOVE_DE], [All_returns_DC], [0001K_AND_UNDER_DC], [0001K_TO_0025K_DC], [0025K_TO_0050K_DC], [0050K_TO_0075K_DC], [0075K_TO_0100K_DC], [0100K_TO_0200K_DC], [0200K_TO_0500K_DC], [0500K_TO_1000K_DC], [1000K_AND_ABOVE_DC], [All_returns_FL], [0001K_AND_UNDER_FL], [0001K_TO_0025K_FL], [0025K_TO_0050K_FL], [0050K_TO_0075K_FL], [0075K_TO_0100K_FL], [0100K_TO_0200K_FL], [0200K_TO_0500K_FL], [0500K_TO_1000K_FL], [1000K_AND_ABOVE_FL], [All_returns_GA], [0001K_AND_UNDER_GA], [0001K_TO_0025K_GA], [0025K_TO_0050K_GA], [0050K_TO_0075K_GA], [0075K_TO_0100K_GA], [0100K_TO_0200K_GA], [0200K_TO_0500K_GA], [0500K_TO_1000K_GA], [1000K_AND_ABOVE_GA], [All_returns_HI], [0001K_AND_UNDER_HI], [0001K_TO_0025K_HI], [0025K_TO_0050K_HI], [0050K_TO_0075K_HI], [0075K_TO_0100K_HI], [0100K_TO_0200K_HI], [0200K_TO_0500K_HI], [0500K_TO_1000K_HI], [1000K_AND_ABOVE_HI], [All_returns_ID], [0001K_AND_UNDER_ID], [0001K_TO_0025K_ID], [0025K_TO_0050K_ID], [0050K_TO_0075K_ID], [0075K_TO_0100K_ID], [0100K_TO_0200K_ID], [0200K_TO_0500K_ID], [0500K_TO_1000K_ID], [1000K_AND_ABOVE_ID], [All_returns_IL], [0001K_AND_UNDER_IL], [0001K_TO_0025K_IL], [0025K_TO_0050K_IL], [0050K_TO_0075K_IL], [0075K_TO_0100K_IL], [0100K_TO_0200K_IL], [0200K_TO_0500K_IL], [0500K_TO_1000K_IL], [1000K_AND_ABOVE_IL], [All_returns_IN], [0001K_AND_UNDER_IN], [0001K_TO_0025K_IN], [0025K_TO_0050K_IN], [0050K_TO_0075K_IN], [0075K_TO_0100K_IN], [0100K_TO_0200K_IN], [0200K_TO_0500K_IN], [0500K_TO_1000K_IN], [1000K_AND_ABOVE_IN], [All_returns_IA], [0001K_AND_UNDER_IA], [0001K_TO_0025K_IA], [0025K_TO_0050K_IA], [0050K_TO_0075K_IA], [0075K_TO_0100K_IA], [0100K_TO_0200K_IA], [0200K_TO_0500K_IA], [0500K_TO_1000K_IA], [1000K_AND_ABOVE_IA], [All_returns_KS], [0001K_AND_UNDER_KS], [0001K_TO_0025K_KS], [0025K_TO_0050K_KS], [0050K_TO_0075K_KS], [0075K_TO_0100K_KS], [0100K_TO_0200K_KS], [0200K_TO_0500K_KS], [0500K_TO_1000K_KS], [1000K_AND_ABOVE_KS], [All_returns_KY], [0001K_AND_UNDER_KY], [0001K_TO_0025K_KY], [0025K_TO_0050K_KY], [0050K_TO_0075K_KY], [0075K_TO_0100K_KY], [0100K_TO_0200K_KY], [0200K_TO_0500K_KY], [0500K_TO_1000K_KY], [1000K_AND_ABOVE_KY], [All_returns_LA], [0001K_AND_UNDER_LA], [0001K_TO_0025K_LA], [0025K_TO_0050K_LA], [0050K_TO_0075K_LA], [0075K_TO_0100K_LA], [0100K_TO_0200K_LA], [0200K_TO_0500K_LA], [0500K_TO_1000K_LA], [1000K_AND_ABOVE_LA], [All_returns_ME], [0001K_AND_UNDER_ME], [0001K_TO_0025K_ME], [0025K_TO_0050K_ME], [0050K_TO_0075K_ME], [0075K_TO_0100K_ME], [0100K_TO_0200K_ME], [0200K_TO_0500K_ME], [0500K_TO_1000K_ME], [1000K_AND_ABOVE_ME], [All_returns_MD], [0001K_AND_UNDER_MD], [0001K_TO_0025K_MD], [0025K_TO_0050K_MD], [0050K_TO_0075K_MD], [0075K_TO_0100K_MD], [0100K_TO_0200K_MD], [0200K_TO_0500K_MD], [0500K_TO_1000K_MD], [1000K_AND_ABOVE_MD], [All_returns_MA], [0001K_AND_UNDER_MA], [0001K_TO_0025K_MA], [0025K_TO_0050K_MA], [0050K_TO_0075K_MA], [0075K_TO_0100K_MA], [0100K_TO_0200K_MA], [0200K_TO_0500K_MA], [0500K_TO_1000K_MA], [1000K_AND_ABOVE_MA], [All_returns_MI], [0001K_AND_UNDER_MI], [0001K_TO_0025K_MI], [0025K_TO_0050K_MI], [0050K_TO_0075K_MI], [0075K_TO_0100K_MI], [0100K_TO_0200K_MI], [0200K_TO_0500K_MI], [0500K_TO_1000K_MI], [1000K_AND_ABOVE_MI], [All_returns_MN], [0001K_AND_UNDER_MN], [0001K_TO_0025K_MN], [0025K_TO_0050K_MN], [0050K_TO_0075K_MN], [0075K_TO_0100K_MN], [0100K_TO_0200K_MN], [0200K_TO_0500K_MN], [0500K_TO_1000K_MN], [1000K_AND_ABOVE_MN], [All_returns_MS], [0001K_AND_UNDER_MS], [0001K_TO_0025K_MS], [0025K_TO_0050K_MS], [0050K_TO_0075K_MS], [0075K_TO_0100K_MS], [0100K_TO_0200K_MS], [0200K_TO_0500K_MS], [0500K_TO_1000K_MS], [1000K_AND_ABOVE_MS], [All_returns_MO], [0001K_AND_UNDER_MO], [0001K_TO_0025K_MO], [0025K_TO_0050K_MO], [0050K_TO_0075K_MO], [0075K_TO_0100K_MO], [0100K_TO_0200K_MO], [0200K_TO_0500K_MO], [0500K_TO_1000K_MO], [1000K_AND_ABOVE_MO], [All_returns_MT], [0001K_AND_UNDER_MT], [0001K_TO_0025K_MT], [0025K_TO_0050K_MT], [0050K_TO_0075K_MT], [0075K_TO_0100K_MT], [0100K_TO_0200K_MT], [0200K_TO_0500K_MT], [0500K_TO_1000K_MT], [1000K_AND_ABOVE_MT], [All_returns_NE], [0001K_AND_UNDER_NE], [0001K_TO_0025K_NE], [0025K_TO_0050K_NE], [0050K_TO_0075K_NE], [0075K_TO_0100K_NE], [0100K_TO_0200K_NE], [0200K_TO_0500K_NE], [0500K_TO_1000K_NE], [1000K_AND_ABOVE_NE], [All_returns_NV], [0001K_AND_UNDER_NV], [0001K_TO_0025K_NV], [0025K_TO_0050K_NV], [0050K_TO_0075K_NV], [0075K_TO_0100K_NV], [0100K_TO_0200K_NV], [0200K_TO_0500K_NV], [0500K_TO_1000K_NV], [1000K_AND_ABOVE_NV], [All_returns_NH], [0001K_AND_UNDER_NH], [0001K_TO_0025K_NH], [0025K_TO_0050K_NH], [0050K_TO_0075K_NH], [0075K_TO_0100K_NH], [0100K_TO_0200K_NH], [0200K_TO_0500K_NH], [0500K_TO_1000K_NH], [1000K_AND_ABOVE_NH], [All_returns_NJ], [0001K_AND_UNDER_NJ], [0001K_TO_0025K_NJ], [0025K_TO_0050K_NJ], [0050K_TO_0075K_NJ], [0075K_TO_0100K_NJ], [0100K_TO_0200K_NJ], [0200K_TO_0500K_NJ], [0500K_TO_1000K_NJ], [1000K_AND_ABOVE_NJ], [All_returns_NM], [0001K_AND_UNDER_NM], [0001K_TO_0025K_NM], [0025K_TO_0050K_NM], [0050K_TO_0075K_NM], [0075K_TO_0100K_NM], [0100K_TO_0200K_NM], [0200K_TO_0500K_NM], [0500K_TO_1000K_NM], [1000K_AND_ABOVE_NM], [All_returns_NY], [0001K_AND_UNDER_NY], [0001K_TO_0025K_NY], [0025K_TO_0050K_NY], [0050K_TO_0075K_NY], [0075K_TO_0100K_NY], [0100K_TO_0200K_NY], [0200K_TO_0500K_NY], [0500K_TO_1000K_NY], [1000K_AND_ABOVE_NY], [All_returns_NC], [0001K_AND_UNDER_NC], [0001K_TO_0025K_NC], [0025K_TO_0050K_NC], [0050K_TO_0075K_NC], [0075K_TO_0100K_NC], [0100K_TO_0200K_NC], [0200K_TO_0500K_NC], [0500K_TO_1000K_NC], [1000K_AND_ABOVE_NC], [All_returns_ND], [0001K_AND_UNDER_ND], [0001K_TO_0025K_ND], [0025K_TO_0050K_ND], [0050K_TO_0075K_ND], [0075K_TO_0100K_ND], [0100K_TO_0200K_ND], [0200K_TO_0500K_ND], [0500K_TO_1000K_ND], [1000K_AND_ABOVE_ND], [All_returns_OH], [0001K_AND_UNDER_OH], [0001K_TO_0025K_OH], [0025K_TO_0050K_OH], [0050K_TO_0075K_OH], [0075K_TO_0100K_OH], [0100K_TO_0200K_OH], [0200K_TO_0500K_OH], [0500K_TO_1000K_OH], [1000K_AND_ABOVE_OH], [All_returns_OK], [0001K_AND_UNDER_OK], [0001K_TO_0025K_OK], [0025K_TO_0050K_OK], [0050K_TO_0075K_OK], [0075K_TO_0100K_OK], [0100K_TO_0200K_OK], [0200K_TO_0500K_OK], [0500K_TO_1000K_OK], [1000K_AND_ABOVE_OK], [All_returns_OR], [0001K_AND_UNDER_OR], [0001K_TO_0025K_OR], [0025K_TO_0050K_OR], [0050K_TO_0075K_OR], [0075K_TO_0100K_OR], [0100K_TO_0200K_OR], [0200K_TO_0500K_OR], [0500K_TO_1000K_OR], [1000K_AND_ABOVE_OR], [All_returns_PA], [0001K_AND_UNDER_PA], [0001K_TO_0025K_PA], [0025K_TO_0050K_PA], [0050K_TO_0075K_PA], [0075K_TO_0100K_PA], [0100K_TO_0200K_PA], [0200K_TO_0500K_PA], [0500K_TO_1000K_PA], [1000K_AND_ABOVE_PA], [All_returns_RI], [0001K_AND_UNDER_RI], [0001K_TO_0025K_RI], [0025K_TO_0050K_RI], [0050K_TO_0075K_RI], [0075K_TO_0100K_RI], [0100K_TO_0200K_RI], [0200K_TO_0500K_RI], [0500K_TO_1000K_RI], [1000K_AND_ABOVE_RI], [All_returns_SC], [0001K_AND_UNDER_SC], [0001K_TO_0025K_SC], [0025K_TO_0050K_SC], [0050K_TO_0075K_SC], [0075K_TO_0100K_SC], [0100K_TO_0200K_SC], [0200K_TO_0500K_SC], [0500K_TO_1000K_SC], [1000K_AND_ABOVE_SC], [All_returns_SD], [0001K_AND_UNDER_SD], [0001K_TO_0025K_SD], [0025K_TO_0050K_SD], [0050K_TO_0075K_SD], [0075K_TO_0100K_SD], [0100K_TO_0200K_SD], [0200K_TO_0500K_SD], [0500K_TO_1000K_SD], [1000K_AND_ABOVE_SD], [All_returns_TN], [0001K_AND_UNDER_TN], [0001K_TO_0025K_TN], [0025K_TO_0050K_TN], [0050K_TO_0075K_TN], [0075K_TO_0100K_TN], [0100K_TO_0200K_TN], [0200K_TO_0500K_TN], [0500K_TO_1000K_TN], [1000K_AND_ABOVE_TN], [All_returns_TX], [0001K_AND_UNDER_TX], [0001K_TO_0025K_TX], [0025K_TO_0050K_TX], [0050K_TO_0075K_TX], [0075K_TO_0100K_TX], [0100K_TO_0200K_TX], [0200K_TO_0500K_TX], [0500K_TO_1000K_TX], [1000K_AND_ABOVE_TX], [All_returns_UT], [0001K_AND_UNDER_UT], [0001K_TO_0025K_UT], [0025K_TO_0050K_UT], [0050K_TO_0075K_UT], [0075K_TO_0100K_UT], [0100K_TO_0200K_UT], [0200K_TO_0500K_UT], [0500K_TO_1000K_UT], [1000K_AND_ABOVE_UT], [All_returns_VT], [0001K_AND_UNDER_VT], [0001K_TO_0025K_VT], [0025K_TO_0050K_VT], [0050K_TO_0075K_VT], [0075K_TO_0100K_VT], [0100K_TO_0200K_VT], [0200K_TO_0500K_VT], [0500K_TO_1000K_VT], [1000K_AND_ABOVE_VT], [All_returns_VA], [0001K_AND_UNDER_VA], [0001K_TO_0025K_VA], [0025K_TO_0050K_VA], [0050K_TO_0075K_VA], [0075K_TO_0100K_VA], [0100K_TO_0200K_VA], [0200K_TO_0500K_VA], [0500K_TO_1000K_VA], [1000K_AND_ABOVE_VA], [All_returns_WA], [0001K_AND_UNDER_WA], [0001K_TO_0025K_WA], [0025K_TO_0050K_WA], [0050K_TO_0075K_WA], [0075K_TO_0100K_WA], [0100K_TO_0200K_WA], [0200K_TO_0500K_WA], [0500K_TO_1000K_WA], [1000K_AND_ABOVE_WA], [All_returns_WV], [0001K_AND_UNDER_WV], [0001K_TO_0025K_WV], [0025K_TO_0050K_WV], [0050K_TO_0075K_WV], [0075K_TO_0100K_WV], [0100K_TO_0200K_WV], [0200K_TO_0500K_WV], [0500K_TO_1000K_WV], [1000K_AND_ABOVE_WV], [All_returns_WI], [0001K_AND_UNDER_WI], [0001K_TO_0025K_WI], [0025K_TO_0050K_WI], [0050K_TO_0075K_WI], [0075K_TO_0100K_WI], [0100K_TO_0200K_WI], [0200K_TO_0500K_WI], [0500K_TO_1000K_WI], [1000K_AND_ABOVE_WI], [All_returns_WY], [0001K_AND_UNDER_WY], [0001K_TO_0025K_WY], [0025K_TO_0050K_WY], [0050K_TO_0075K_WY], [0075K_TO_0100K_WY], [0100K_TO_0200K_WY], [0200K_TO_0500K_WY], [0500K_TO_1000K_WY], [1000K_AND_ABOVE_WY])
) U
;	

-- Drop observations where adjusted gross income = "all returns" to avoid double counting 
DELETE FROM TEMP_SOI_STATE_2010_2011_1 WHERE AGI_SIZE = 'All_returns';

-- Set data type for [value] as float 
ALTER TABLE TEMP_SOI_STATE_2010_2011_1 ALTER COLUMN VALUE FLOAT;

-- Pivot 
SELECT				*
INTO				TEMP_SOI_STATE_2010_2011_2
FROM				TEMP_SOI_STATE_2010_2011_1
PIVOT
(
SUM(VALUE) FOR ITEM IN ([Nb_returns], [Nb_joint_returns], [AGI], [Itemized_deductions_nb], [Itemized_deductions_amt], [Contributions_nb], [Contributions_amt])
) AS A
;

-- Aggregate by standardized tax brackets (The level of granularity of the tax brackets varies over time in the IRS data. I create standardized brackets that constcuted for all years in my sample)
SELECT				*,
					TAX_BRACKET = 
				CASE
					WHEN 	AGI_SIZE = '0001K_AND_UNDER'
						OR	AGI_SIZE = '0001K_TO_0025K'
						OR	AGI_SIZE = '0025K_TO_0050K'
					THEN 	'0050K_AND_UNDER'			
					
					WHEN	AGI_SIZE = '0200K_TO_0500K'
						OR	AGI_SIZE = '0500K_TO_1000K'
						OR	AGI_SIZE = '1000K_AND_ABOVE' 
					THEN	'0200K_AND_ABOVE'	
					
					ELSE	AGI_SIZE
				END
INTO				TEMP_SOI_STATE_2010_2011_3
FROM				TEMP_SOI_STATE_2010_2011_2
;

-- Aggregate by state-tax bracket-year
SELECT				FISCAL_YEAR                                           ,
					STATE                                                 ,
					TAX_BRACKET                                           ,
					Nb_returns 				= SUM(Nb_returns)             ,
					Nb_joint_returns		= SUM(Nb_joint_returns)       ,
					AGI                     = SUM(AGI)                    ,
					Itemized_deductions_nb  = SUM(Itemized_deductions_nb) ,
					Itemized_deductions_amt = SUM(Itemized_deductions_amt),	
					Contributions_nb        = SUM(Contributions_nb)       ,
					Contributions_amt       = SUM(Contributions_amt)
INTO				TEMP_SOI_STATE_2010_2011					
FROM				TEMP_SOI_STATE_2010_2011_3
GROUP BY			FISCAL_YEAR,
					STATE,
					TAX_BRACKET		
;

/* Format data for years 2012-2011 */
-- Unpivot
SELECT				FISCAL_YEAR = YEAR,
					STATE = RIGHT(AGI_SIZE,2),
					ITEM,	
					AGI_SIZE = LEFT(AGI_SIZE, LEN(AGI_SIZE)-3),
					VALUE		
INTO				TEMP_SOI_STATE_2012_2015_1					
FROM				JMP_IRS_SOI_ITEMIZED_STATE.dbo.STATE_2012_2015
UNPIVOT
(
	VALUE FOR AGI_SIZE IN ([All_returns_AL], [0001K_AND_UNDER_AL], [0001K_TO_0010K_AL], [0010K_TO_0025K_AL], [0025K_TO_0050K_AL], [0050K_TO_0075K_AL], [0075K_TO_0100K_AL], [0100K_TO_0200K_AL], [0200K_TO_0500K_AL], [0500K_TO_1000K_AL], [1000K_AND_ABOVE_AL], [All_returns_AK], [0001K_AND_UNDER_AK], [0001K_TO_0010K_AK], [0010K_TO_0025K_AK], [0025K_TO_0050K_AK], [0050K_TO_0075K_AK], [0075K_TO_0100K_AK], [0100K_TO_0200K_AK], [0200K_TO_0500K_AK], [0500K_TO_1000K_AK], [1000K_AND_ABOVE_AK], [All_returns_AZ], [0001K_AND_UNDER_AZ], [0001K_TO_0010K_AZ], [0010K_TO_0025K_AZ], [0025K_TO_0050K_AZ], [0050K_TO_0075K_AZ], [0075K_TO_0100K_AZ], [0100K_TO_0200K_AZ], [0200K_TO_0500K_AZ], [0500K_TO_1000K_AZ], [1000K_AND_ABOVE_AZ], [All_returns_AR], [0001K_AND_UNDER_AR], [0001K_TO_0010K_AR], [0010K_TO_0025K_AR], [0025K_TO_0050K_AR], [0050K_TO_0075K_AR], [0075K_TO_0100K_AR], [0100K_TO_0200K_AR], [0200K_TO_0500K_AR], [0500K_TO_1000K_AR], [1000K_AND_ABOVE_AR], [All_returns_CA], [0001K_AND_UNDER_CA], [0001K_TO_0010K_CA], [0010K_TO_0025K_CA], [0025K_TO_0050K_CA], [0050K_TO_0075K_CA], [0075K_TO_0100K_CA], [0100K_TO_0200K_CA], [0200K_TO_0500K_CA], [0500K_TO_1000K_CA], [1000K_AND_ABOVE_CA], [All_returns_CO], [0001K_AND_UNDER_CO], [0001K_TO_0010K_CO], [0010K_TO_0025K_CO], [0025K_TO_0050K_CO], [0050K_TO_0075K_CO], [0075K_TO_0100K_CO], [0100K_TO_0200K_CO], [0200K_TO_0500K_CO], [0500K_TO_1000K_CO], [1000K_AND_ABOVE_CO], [All_returns_CT], [0001K_AND_UNDER_CT], [0001K_TO_0010K_CT], [0010K_TO_0025K_CT], [0025K_TO_0050K_CT], [0050K_TO_0075K_CT], [0075K_TO_0100K_CT], [0100K_TO_0200K_CT], [0200K_TO_0500K_CT], [0500K_TO_1000K_CT], [1000K_AND_ABOVE_CT], [All_returns_DE], [0001K_AND_UNDER_DE], [0001K_TO_0010K_DE], [0010K_TO_0025K_DE], [0025K_TO_0050K_DE], [0050K_TO_0075K_DE], [0075K_TO_0100K_DE], [0100K_TO_0200K_DE], [0200K_TO_0500K_DE], [0500K_TO_1000K_DE], [1000K_AND_ABOVE_DE], [All_returns_DC], [0001K_AND_UNDER_DC], [0001K_TO_0010K_DC], [0010K_TO_0025K_DC], [0025K_TO_0050K_DC], [0050K_TO_0075K_DC], [0075K_TO_0100K_DC], [0100K_TO_0200K_DC], [0200K_TO_0500K_DC], [0500K_TO_1000K_DC], [1000K_AND_ABOVE_DC], [All_returns_FL], [0001K_AND_UNDER_FL], [0001K_TO_0010K_FL], [0010K_TO_0025K_FL], [0025K_TO_0050K_FL], [0050K_TO_0075K_FL], [0075K_TO_0100K_FL], [0100K_TO_0200K_FL], [0200K_TO_0500K_FL], [0500K_TO_1000K_FL], [1000K_AND_ABOVE_FL], [All_returns_GA], [0001K_AND_UNDER_GA], [0001K_TO_0010K_GA], [0010K_TO_0025K_GA], [0025K_TO_0050K_GA], [0050K_TO_0075K_GA], [0075K_TO_0100K_GA], [0100K_TO_0200K_GA], [0200K_TO_0500K_GA], [0500K_TO_1000K_GA], [1000K_AND_ABOVE_GA], [All_returns_HI], [0001K_AND_UNDER_HI], [0001K_TO_0010K_HI], [0010K_TO_0025K_HI], [0025K_TO_0050K_HI], [0050K_TO_0075K_HI], [0075K_TO_0100K_HI], [0100K_TO_0200K_HI], [0200K_TO_0500K_HI], [0500K_TO_1000K_HI], [1000K_AND_ABOVE_HI], [All_returns_ID], [0001K_AND_UNDER_ID], [0001K_TO_0010K_ID], [0010K_TO_0025K_ID], [0025K_TO_0050K_ID], [0050K_TO_0075K_ID], [0075K_TO_0100K_ID], [0100K_TO_0200K_ID], [0200K_TO_0500K_ID], [0500K_TO_1000K_ID], [1000K_AND_ABOVE_ID], [All_returns_IL], [0001K_AND_UNDER_IL], [0001K_TO_0010K_IL], [0010K_TO_0025K_IL], [0025K_TO_0050K_IL], [0050K_TO_0075K_IL], [0075K_TO_0100K_IL], [0100K_TO_0200K_IL], [0200K_TO_0500K_IL], [0500K_TO_1000K_IL], [1000K_AND_ABOVE_IL], [All_returns_IN], [0001K_AND_UNDER_IN], [0001K_TO_0010K_IN], [0010K_TO_0025K_IN], [0025K_TO_0050K_IN], [0050K_TO_0075K_IN], [0075K_TO_0100K_IN], [0100K_TO_0200K_IN], [0200K_TO_0500K_IN], [0500K_TO_1000K_IN], [1000K_AND_ABOVE_IN], [All_returns_IA], [0001K_AND_UNDER_IA], [0001K_TO_0010K_IA], [0010K_TO_0025K_IA], [0025K_TO_0050K_IA], [0050K_TO_0075K_IA], [0075K_TO_0100K_IA], [0100K_TO_0200K_IA], [0200K_TO_0500K_IA], [0500K_TO_1000K_IA], [1000K_AND_ABOVE_IA], [All_returns_KS], [0001K_AND_UNDER_KS], [0001K_TO_0010K_KS], [0010K_TO_0025K_KS], [0025K_TO_0050K_KS], [0050K_TO_0075K_KS], [0075K_TO_0100K_KS], [0100K_TO_0200K_KS], [0200K_TO_0500K_KS], [0500K_TO_1000K_KS], [1000K_AND_ABOVE_KS], [All_returns_KY], [0001K_AND_UNDER_KY], [0001K_TO_0010K_KY], [0010K_TO_0025K_KY], [0025K_TO_0050K_KY], [0050K_TO_0075K_KY], [0075K_TO_0100K_KY], [0100K_TO_0200K_KY], [0200K_TO_0500K_KY], [0500K_TO_1000K_KY], [1000K_AND_ABOVE_KY], [All_returns_LA], [0001K_AND_UNDER_LA], [0001K_TO_0010K_LA], [0010K_TO_0025K_LA], [0025K_TO_0050K_LA], [0050K_TO_0075K_LA], [0075K_TO_0100K_LA], [0100K_TO_0200K_LA], [0200K_TO_0500K_LA], [0500K_TO_1000K_LA], [1000K_AND_ABOVE_LA], [All_returns_ME], [0001K_AND_UNDER_ME], [0001K_TO_0010K_ME], [0010K_TO_0025K_ME], [0025K_TO_0050K_ME], [0050K_TO_0075K_ME], [0075K_TO_0100K_ME], [0100K_TO_0200K_ME], [0200K_TO_0500K_ME], [0500K_TO_1000K_ME], [1000K_AND_ABOVE_ME], [All_returns_MD], [0001K_AND_UNDER_MD], [0001K_TO_0010K_MD], [0010K_TO_0025K_MD], [0025K_TO_0050K_MD], [0050K_TO_0075K_MD], [0075K_TO_0100K_MD], [0100K_TO_0200K_MD], [0200K_TO_0500K_MD], [0500K_TO_1000K_MD], [1000K_AND_ABOVE_MD], [All_returns_MA], [0001K_AND_UNDER_MA], [0001K_TO_0010K_MA], [0010K_TO_0025K_MA], [0025K_TO_0050K_MA], [0050K_TO_0075K_MA], [0075K_TO_0100K_MA], [0100K_TO_0200K_MA], [0200K_TO_0500K_MA], [0500K_TO_1000K_MA], [1000K_AND_ABOVE_MA], [All_returns_MI], [0001K_AND_UNDER_MI], [0001K_TO_0010K_MI], [0010K_TO_0025K_MI], [0025K_TO_0050K_MI], [0050K_TO_0075K_MI], [0075K_TO_0100K_MI], [0100K_TO_0200K_MI], [0200K_TO_0500K_MI], [0500K_TO_1000K_MI], [1000K_AND_ABOVE_MI], [All_returns_MN], [0001K_AND_UNDER_MN], [0001K_TO_0010K_MN], [0010K_TO_0025K_MN], [0025K_TO_0050K_MN], [0050K_TO_0075K_MN], [0075K_TO_0100K_MN], [0100K_TO_0200K_MN], [0200K_TO_0500K_MN], [0500K_TO_1000K_MN], [1000K_AND_ABOVE_MN], [All_returns_MS], [0001K_AND_UNDER_MS], [0001K_TO_0010K_MS], [0010K_TO_0025K_MS], [0025K_TO_0050K_MS], [0050K_TO_0075K_MS], [0075K_TO_0100K_MS], [0100K_TO_0200K_MS], [0200K_TO_0500K_MS], [0500K_TO_1000K_MS], [1000K_AND_ABOVE_MS], [All_returns_MO], [0001K_AND_UNDER_MO], [0001K_TO_0010K_MO], [0010K_TO_0025K_MO], [0025K_TO_0050K_MO], [0050K_TO_0075K_MO], [0075K_TO_0100K_MO], [0100K_TO_0200K_MO], [0200K_TO_0500K_MO], [0500K_TO_1000K_MO], [1000K_AND_ABOVE_MO], [All_returns_MT], [0001K_AND_UNDER_MT], [0001K_TO_0010K_MT], [0010K_TO_0025K_MT], [0025K_TO_0050K_MT], [0050K_TO_0075K_MT], [0075K_TO_0100K_MT], [0100K_TO_0200K_MT], [0200K_TO_0500K_MT], [0500K_TO_1000K_MT], [1000K_AND_ABOVE_MT], [All_returns_NE], [0001K_AND_UNDER_NE], [0001K_TO_0010K_NE], [0010K_TO_0025K_NE], [0025K_TO_0050K_NE], [0050K_TO_0075K_NE], [0075K_TO_0100K_NE], [0100K_TO_0200K_NE], [0200K_TO_0500K_NE], [0500K_TO_1000K_NE], [1000K_AND_ABOVE_NE], [All_returns_NV], [0001K_AND_UNDER_NV], [0001K_TO_0010K_NV], [0010K_TO_0025K_NV], [0025K_TO_0050K_NV], [0050K_TO_0075K_NV], [0075K_TO_0100K_NV], [0100K_TO_0200K_NV], [0200K_TO_0500K_NV], [0500K_TO_1000K_NV], [1000K_AND_ABOVE_NV], [All_returns_NH], [0001K_AND_UNDER_NH], [0001K_TO_0010K_NH], [0010K_TO_0025K_NH], [0025K_TO_0050K_NH], [0050K_TO_0075K_NH], [0075K_TO_0100K_NH], [0100K_TO_0200K_NH], [0200K_TO_0500K_NH], [0500K_TO_1000K_NH], [1000K_AND_ABOVE_NH], [All_returns_NJ], [0001K_AND_UNDER_NJ], [0001K_TO_0010K_NJ], [0010K_TO_0025K_NJ], [0025K_TO_0050K_NJ], [0050K_TO_0075K_NJ], [0075K_TO_0100K_NJ], [0100K_TO_0200K_NJ], [0200K_TO_0500K_NJ], [0500K_TO_1000K_NJ], [1000K_AND_ABOVE_NJ], [All_returns_NM], [0001K_AND_UNDER_NM], [0001K_TO_0010K_NM], [0010K_TO_0025K_NM], [0025K_TO_0050K_NM], [0050K_TO_0075K_NM], [0075K_TO_0100K_NM], [0100K_TO_0200K_NM], [0200K_TO_0500K_NM], [0500K_TO_1000K_NM], [1000K_AND_ABOVE_NM], [All_returns_NY], [0001K_AND_UNDER_NY], [0001K_TO_0010K_NY], [0010K_TO_0025K_NY], [0025K_TO_0050K_NY], [0050K_TO_0075K_NY], [0075K_TO_0100K_NY], [0100K_TO_0200K_NY], [0200K_TO_0500K_NY], [0500K_TO_1000K_NY], [1000K_AND_ABOVE_NY], [All_returns_NC], [0001K_AND_UNDER_NC], [0001K_TO_0010K_NC], [0010K_TO_0025K_NC], [0025K_TO_0050K_NC], [0050K_TO_0075K_NC], [0075K_TO_0100K_NC], [0100K_TO_0200K_NC], [0200K_TO_0500K_NC], [0500K_TO_1000K_NC], [1000K_AND_ABOVE_NC], [All_returns_ND], [0001K_AND_UNDER_ND], [0001K_TO_0010K_ND], [0010K_TO_0025K_ND], [0025K_TO_0050K_ND], [0050K_TO_0075K_ND], [0075K_TO_0100K_ND], [0100K_TO_0200K_ND], [0200K_TO_0500K_ND], [0500K_TO_1000K_ND], [1000K_AND_ABOVE_ND], [All_returns_OH], [0001K_AND_UNDER_OH], [0001K_TO_0010K_OH], [0010K_TO_0025K_OH], [0025K_TO_0050K_OH], [0050K_TO_0075K_OH], [0075K_TO_0100K_OH], [0100K_TO_0200K_OH], [0200K_TO_0500K_OH], [0500K_TO_1000K_OH], [1000K_AND_ABOVE_OH], [All_returns_OK], [0001K_AND_UNDER_OK], [0001K_TO_0010K_OK], [0010K_TO_0025K_OK], [0025K_TO_0050K_OK], [0050K_TO_0075K_OK], [0075K_TO_0100K_OK], [0100K_TO_0200K_OK], [0200K_TO_0500K_OK], [0500K_TO_1000K_OK], [1000K_AND_ABOVE_OK], [All_returns_OR], [0001K_AND_UNDER_OR], [0001K_TO_0010K_OR], [0010K_TO_0025K_OR], [0025K_TO_0050K_OR], [0050K_TO_0075K_OR], [0075K_TO_0100K_OR], [0100K_TO_0200K_OR], [0200K_TO_0500K_OR], [0500K_TO_1000K_OR], [1000K_AND_ABOVE_OR], [All_returns_PA], [0001K_AND_UNDER_PA], [0001K_TO_0010K_PA], [0010K_TO_0025K_PA], [0025K_TO_0050K_PA], [0050K_TO_0075K_PA], [0075K_TO_0100K_PA], [0100K_TO_0200K_PA], [0200K_TO_0500K_PA], [0500K_TO_1000K_PA], [1000K_AND_ABOVE_PA], [All_returns_RI], [0001K_AND_UNDER_RI], [0001K_TO_0010K_RI], [0010K_TO_0025K_RI], [0025K_TO_0050K_RI], [0050K_TO_0075K_RI], [0075K_TO_0100K_RI], [0100K_TO_0200K_RI], [0200K_TO_0500K_RI], [0500K_TO_1000K_RI], [1000K_AND_ABOVE_RI], [All_returns_SC], [0001K_AND_UNDER_SC], [0001K_TO_0010K_SC], [0010K_TO_0025K_SC], [0025K_TO_0050K_SC], [0050K_TO_0075K_SC], [0075K_TO_0100K_SC], [0100K_TO_0200K_SC], [0200K_TO_0500K_SC], [0500K_TO_1000K_SC], [1000K_AND_ABOVE_SC], [All_returns_SD], [0001K_AND_UNDER_SD], [0001K_TO_0010K_SD], [0010K_TO_0025K_SD], [0025K_TO_0050K_SD], [0050K_TO_0075K_SD], [0075K_TO_0100K_SD], [0100K_TO_0200K_SD], [0200K_TO_0500K_SD], [0500K_TO_1000K_SD], [1000K_AND_ABOVE_SD], [All_returns_TN], [0001K_AND_UNDER_TN], [0001K_TO_0010K_TN], [0010K_TO_0025K_TN], [0025K_TO_0050K_TN], [0050K_TO_0075K_TN], [0075K_TO_0100K_TN], [0100K_TO_0200K_TN], [0200K_TO_0500K_TN], [0500K_TO_1000K_TN], [1000K_AND_ABOVE_TN], [All_returns_TX], [0001K_AND_UNDER_TX], [0001K_TO_0010K_TX], [0010K_TO_0025K_TX], [0025K_TO_0050K_TX], [0050K_TO_0075K_TX], [0075K_TO_0100K_TX], [0100K_TO_0200K_TX], [0200K_TO_0500K_TX], [0500K_TO_1000K_TX], [1000K_AND_ABOVE_TX], [All_returns_UT], [0001K_AND_UNDER_UT], [0001K_TO_0010K_UT], [0010K_TO_0025K_UT], [0025K_TO_0050K_UT], [0050K_TO_0075K_UT], [0075K_TO_0100K_UT], [0100K_TO_0200K_UT], [0200K_TO_0500K_UT], [0500K_TO_1000K_UT], [1000K_AND_ABOVE_UT], [All_returns_VT], [0001K_AND_UNDER_VT], [0001K_TO_0010K_VT], [0010K_TO_0025K_VT], [0025K_TO_0050K_VT], [0050K_TO_0075K_VT], [0075K_TO_0100K_VT], [0100K_TO_0200K_VT], [0200K_TO_0500K_VT], [0500K_TO_1000K_VT], [1000K_AND_ABOVE_VT], [All_returns_VA], [0001K_AND_UNDER_VA], [0001K_TO_0010K_VA], [0010K_TO_0025K_VA], [0025K_TO_0050K_VA], [0050K_TO_0075K_VA], [0075K_TO_0100K_VA], [0100K_TO_0200K_VA], [0200K_TO_0500K_VA], [0500K_TO_1000K_VA], [1000K_AND_ABOVE_VA], [All_returns_WA], [0001K_AND_UNDER_WA], [0001K_TO_0010K_WA], [0010K_TO_0025K_WA], [0025K_TO_0050K_WA], [0050K_TO_0075K_WA], [0075K_TO_0100K_WA], [0100K_TO_0200K_WA], [0200K_TO_0500K_WA], [0500K_TO_1000K_WA], [1000K_AND_ABOVE_WA], [All_returns_WV], [0001K_AND_UNDER_WV], [0001K_TO_0010K_WV], [0010K_TO_0025K_WV], [0025K_TO_0050K_WV], [0050K_TO_0075K_WV], [0075K_TO_0100K_WV], [0100K_TO_0200K_WV], [0200K_TO_0500K_WV], [0500K_TO_1000K_WV], [1000K_AND_ABOVE_WV], [All_returns_WI], [0001K_AND_UNDER_WI], [0001K_TO_0010K_WI], [0010K_TO_0025K_WI], [0025K_TO_0050K_WI], [0050K_TO_0075K_WI], [0075K_TO_0100K_WI], [0100K_TO_0200K_WI], [0200K_TO_0500K_WI], [0500K_TO_1000K_WI], [1000K_AND_ABOVE_WI], [All_returns_WY], [0001K_AND_UNDER_WY], [0001K_TO_0010K_WY], [0010K_TO_0025K_WY], [0025K_TO_0050K_WY], [0050K_TO_0075K_WY], [0075K_TO_0100K_WY], [0100K_TO_0200K_WY], [0200K_TO_0500K_WY], [0500K_TO_1000K_WY], [1000K_AND_ABOVE_WY])
) U
;	

-- Drop observations where adjusted gross income = "all returns" to avoid double counting 
DELETE FROM TEMP_SOI_STATE_2012_2015_1 WHERE AGI_SIZE = 'All_returns';

-- Set data type for [value] as float 
ALTER TABLE TEMP_SOI_STATE_2012_2015_1 ALTER COLUMN VALUE FLOAT;

-- Pivot 
SELECT				*
INTO				TEMP_SOI_STATE_2012_2015_2
FROM				TEMP_SOI_STATE_2012_2015_1
PIVOT
(
SUM(VALUE) FOR ITEM IN ([Nb_returns], [Nb_joint_returns], [AGI], [Itemized_deductions_nb], [Itemized_deductions_amt], [Contributions_nb], [Contributions_amt])
) AS A
;

-- Aggregate by standardized tax brackets (The level of granularity of the tax brackets varies over time in the IRS data. I create standardized brackets that constcuted for all years in my sample)
SELECT				*,
					TAX_BRACKET = 
				CASE
					WHEN 	AGI_SIZE = '0001K_AND_UNDER'
						OR	AGI_SIZE = '0001K_TO_0010K'
						OR	AGI_SIZE = '0010K_TO_0025K'
						OR	AGI_SIZE = '0025K_TO_0050K'
					THEN 	'0050K_AND_UNDER'				
					
					WHEN	AGI_SIZE = '0200K_TO_0500K'
						OR	AGI_SIZE = '0500K_TO_1000K'
						OR	AGI_SIZE = '1000K_AND_ABOVE' 
					THEN	'0200K_AND_ABOVE'	
					
					ELSE	AGI_SIZE
				END
INTO				TEMP_SOI_STATE_2012_2015_3
FROM				TEMP_SOI_STATE_2012_2015_2
;

-- Aggregate by state-tax bracket-year
SELECT				FISCAL_YEAR                                        ,
					STATE                                                 ,
					TAX_BRACKET                                        ,
					Nb_returns 				= SUM(Nb_returns)             ,
					Nb_joint_returns		= SUM(Nb_joint_returns)       ,
					AGI                     = SUM(AGI)                    ,
					Itemized_deductions_nb  = SUM(Itemized_deductions_nb) ,
					Itemized_deductions_amt = SUM(Itemized_deductions_amt),	
					Contributions_nb        = SUM(Contributions_nb)       ,
					Contributions_amt       = SUM(Contributions_amt)
INTO				TEMP_SOI_STATE_2012_2015					
FROM				TEMP_SOI_STATE_2012_2015_3
GROUP BY			FISCAL_YEAR,
					STATE,
					TAX_BRACKET		
;

/* Merge into a single table */
SELECT				FISCAL_YEAR           ,
					STATE                 ,
					AGI_SIZE = TAX_BRACKET,
					AGI                   ,
					ITEMIZED_DEDUCTIONS_NB,
					CONTRIBUTIONS_NB      
INTO				SOI_STATE_CLEANSED					
FROM				TEMP_SOI_STATE_1997_2001
;

INSERT INTO			SOI_STATE_CLEANSED					
SELECT				FISCAL_YEAR           ,
					STATE                 ,
					TAX_BRACKET           ,
					AGI                   ,
					ITEMIZED_DEDUCTIONS_NB,
					CONTRIBUTIONS_NB      
FROM				TEMP_SOI_STATE_2002_2003
;					
					
INSERT INTO			SOI_STATE_CLEANSED					
SELECT				FISCAL_YEAR           ,
					STATE                 ,
					AGI_SIZE    		  ,
					AGI                   ,
					ITEMIZED_DEDUCTIONS_NB,
					CONTRIBUTIONS_NB      
FROM				TEMP_SOI_STATE_2004_2009
;						
					
INSERT INTO			SOI_STATE_CLEANSED					
SELECT				FISCAL_YEAR           ,
					STATE                 ,
					TAX_BRACKET    		  ,
					AGI                   ,
					ITEMIZED_DEDUCTIONS_NB,
					CONTRIBUTIONS_NB      		
FROM				TEMP_SOI_STATE_2010_2011
;						
					
INSERT INTO			SOI_STATE_CLEANSED					
SELECT				FISCAL_YEAR           ,
					STATE                 ,
					TAX_BRACKET    		  ,
					AGI                   ,
					ITEMIZED_DEDUCTIONS_NB,
					CONTRIBUTIONS_NB      	
FROM				TEMP_SOI_STATE_2012_2015
;						

/* Set data types */
ALTER TABLE SOI_STATE_CLEANSED ALTER COLUMN FISCAL_YEAR    INT;
ALTER TABLE SOI_STATE_CLEANSED ALTER COLUMN STATE          VARCHAR(2);
ALTER TABLE SOI_STATE_CLEANSED ALTER COLUMN AGI_SIZE       VARCHAR(15);

/* Drop intermediary tables */
DROP TABLE TEMP_SOI_STATE_1997_2001_1;
DROP TABLE TEMP_SOI_STATE_1997_2001_2;
DROP TABLE TEMP_SOI_STATE_1997_2001_3;
DROP TABLE TEMP_SOI_STATE_2002_2003_1;
DROP TABLE TEMP_SOI_STATE_2002_2003_2;
DROP TABLE TEMP_SOI_STATE_2002_2003_3;
DROP TABLE TEMP_SOI_STATE_2004_2009_1;
DROP TABLE TEMP_SOI_STATE_2010_2011_1;
DROP TABLE TEMP_SOI_STATE_2010_2011_2;
DROP TABLE TEMP_SOI_STATE_2010_2011_3;
DROP TABLE TEMP_SOI_STATE_2012_2015_1;
DROP TABLE TEMP_SOI_STATE_2012_2015_2;
DROP TABLE TEMP_SOI_STATE_2012_2015_3;
DROP TABLE TEMP_SOI_STATE_1997_2001;
DROP TABLE TEMP_SOI_STATE_2002_2003;
DROP TABLE TEMP_SOI_STATE_2004_2009;
DROP TABLE TEMP_SOI_STATE_2010_2011;
DROP TABLE TEMP_SOI_STATE_2012_2015;

/* **************************************************************************************** */
/* Cleansing IRS county-level data on tax payers (aka statistics of income data)*/	
				
/* Retrieve relevant variables and merge annual tables into a single table */
SELECT				FISCAL_YEAR = 2010                             ,
					FIPS = STATEFIPS + COUNTYFIPS                  ,
					AGI_STUB                                       ,         
					AGI 					= CONVERT(FLOAT,A00100),          
					ITEMIZED_DEDUCTIONS_NB 	= CONVERT(FLOAT,N04470),            
					CONTRIBUTIONS_NB 		= CONVERT(FLOAT,N19700),
					NB_RETURNS 				= CONVERT(FLOAT,N1	  )
INTO				SOI_FIPS_CLEANSED					
FROM				JMP_IRS_SOI_ITEMIZED_COUNTY.dbo.[10incyallagi]
;	

INSERT INTO 		SOI_FIPS_CLEANSED
SELECT				FISCAL_YEAR = 2011                             ,
					FIPS = STATEFIPS + COUNTYFIPS                  ,
					AGI_STUB                                       ,       
					AGI 					= CONVERT(FLOAT,A00100),          
					ITEMIZED_DEDUCTIONS_NB 	= CONVERT(FLOAT,N04470),            
					CONTRIBUTIONS_NB 		= CONVERT(FLOAT,N19700), 
					NB_RETURNS 				= CONVERT(FLOAT,N1    ) 
FROM				JMP_IRS_SOI_ITEMIZED_COUNTY.dbo.[11incyallagi]
;			

INSERT INTO 		SOI_FIPS_CLEANSED				
SELECT				FISCAL_YEAR = 2012                             ,
					FIPS = STATEFIPS + COUNTYFIPS                  ,
					AGI_STUB                                       ,      
					AGI 					= CONVERT(FLOAT,A00100),          
					ITEMIZED_DEDUCTIONS_NB 	= CONVERT(FLOAT,N04470),           
					CONTRIBUTIONS_NB 		= CONVERT(FLOAT,N19700),
					NB_RETURNS 				= CONVERT(FLOAT,N1	  )
FROM				JMP_IRS_SOI_ITEMIZED_COUNTY.dbo.[12cyallagi]
;	

INSERT INTO 		SOI_FIPS_CLEANSED
SELECT				FISCAL_YEAR = 2013                             ,
					FIPS = STATEFIPS + COUNTYFIPS                  ,
					AGI_STUB                                       ,        
					AGI 					= CONVERT(FLOAT,A00100),          
					ITEMIZED_DEDUCTIONS_NB 	= CONVERT(FLOAT,N04470),           
					CONTRIBUTIONS_NB 		= CONVERT(FLOAT,N19700),
					NB_RETURNS 				= CONVERT(FLOAT,N1	  )
FROM				JMP_IRS_SOI_ITEMIZED_COUNTY.dbo.[13incyallagi]
;			

INSERT INTO 		SOI_FIPS_CLEANSED
SELECT				FISCAL_YEAR = 2014                             ,
					FIPS = STATEFIPS + COUNTYFIPS                  ,
					AGI_STUB                                       ,         
					AGI 					= CONVERT(FLOAT,A00100),          
					ITEMIZED_DEDUCTIONS_NB 	= CONVERT(FLOAT,N04470),            
					CONTRIBUTIONS_NB 		= CONVERT(FLOAT,N19700),
					NB_RETURNS 				= CONVERT(FLOAT,N1	  ) 
FROM				JMP_IRS_SOI_ITEMIZED_COUNTY.dbo.[14incyallagi]
;		

INSERT INTO 		SOI_FIPS_CLEANSED
SELECT				FISCAL_YEAR = 2015                             ,
					FIPS = STATEFIPS + COUNTYFIPS                  ,
					AGI_STUB                                       ,     
					AGI 					= CONVERT(FLOAT,A00100),     
					ITEMIZED_DEDUCTIONS_NB 	= CONVERT(FLOAT,N04470),      
					CONTRIBUTIONS_NB 		= CONVERT(FLOAT,N19700),
					NB_RETURNS 				= CONVERT(FLOAT,N1	  )
FROM				JMP_IRS_SOI_ITEMIZED_COUNTY.dbo.[15incyallagi]
;		

/* Drop impossible observations where number of itemizers > number of tax filers (i.e., 34 out of >100K obs) */
DELETE FROM SOI_FIPS_CLEANSED WHERE ITEMIZED_DEDUCTIONS_NB > NB_RETURNS;
	
/* Set data types */
ALTER TABLE SOI_FIPS_CLEANSED ALTER COLUMN FIPS     VARCHAR(5);
ALTER TABLE SOI_FIPS_CLEANSED ALTER COLUMN AGI_STUB VARCHAR(1);

/* Create state-FIPS crosswalk */		
SELECT DISTINCT	STATEFIPS,
				STATE
INTO			STATE_FIPS_CROSSWALK				
FROM			JMP_IRS_SOI_ITEMIZED_COUNTY.dbo.[15incyallagi]
;

/* **************************************************************************************** */
/* Cleansing census data which I use for the "population" variable */	

/* Format data for years 2010-2015 */
-- Retrieve relevant variables
SELECT				STATE 	 = B.State_abbreviation,
					POP_2010 = A.POPESTIMATE2010   ,
					POP_2011 = A.POPESTIMATE2011   ,
					POP_2012 = A.POPESTIMATE2012   ,
					POP_2013 = A.POPESTIMATE2013   ,
					POP_2014 = A.POPESTIMATE2014   ,
					POP_2015 = A.POPESTIMATE2015		
INTO				CENSUS_POP_2010_2015_1					
FROM				JMP_Census_Bureau_Pop_State.dbo.Pop_2010_2017 AS A
				INNER JOIN
					JMP_Census_Bureau_Pop_State.dbo.STATE_ABBREVIATION_handcollected AS B
				ON	A.NAME = B.STATE_NAME
WHERE				A.SUMLEV = '40'
				AND	A.REGION <> 'X'
;

-- Unpivot 
SELECT				STATE                        ,
					YEAR = SUBSTRING(CAPTION,5,4),
					POP  = CONVERT(FLOAT,POP)
INTO				CENSUS_POP_2010_2015					
FROM				CENSUS_POP_2010_2015_1
UNPIVOT
(
POP FOR CAPTION IN (POP_2010, POP_2011, POP_2012, POP_2013, POP_2014, POP_2015)
) U 
;

/* Format data for years 2000-2009 */
-- Retain relevant fields and records 
SELECT				STATE 	 = B.State_abbreviation,
					POP_2000 = A.POPESTIMATE2000   ,
					POP_2001 = A.POPESTIMATE2001   ,
					POP_2002 = A.POPESTIMATE2002   ,
					POP_2003 = A.POPESTIMATE2003   ,
					POP_2004 = A.POPESTIMATE2004   ,
					POP_2005 = A.POPESTIMATE2005   ,
					POP_2006 = A.POPESTIMATE2006   ,
					POP_2007 = A.POPESTIMATE2007   ,
					POP_2008 = A.POPESTIMATE2008   ,
					POP_2009 = A.POPESTIMATE2009		
INTO				CENSUS_POP_2000_2009_1
FROM				JMP_Census_Bureau_Pop_State.dbo.Pop_2000_2010 AS A
				INNER JOIN
					JMP_Census_Bureau_Pop_State.dbo.STATE_ABBREVIATION_handcollected AS B
				ON	A.NAME = B.STATE_NAME
WHERE				A.SEX 		= 0
				AND	A.ORIGIN 	= 0
				AND	A.RACE 		= 0
				AND	A.AGEGRP 	= 0
				AND	A.STATE		<> 0
;

-- Unpivot 
SELECT				STATE,
					YEAR = SUBSTRING(CAPTION,5,4),
					POP  = CONVERT(FLOAT,POP)
INTO				CENSUS_POP_2000_2009					
FROM				CENSUS_POP_2000_2009_1
UNPIVOT
(
POP FOR CAPTION IN (POP_2000, POP_2001, POP_2002, POP_2003, POP_2004, POP_2005, POP_2006, POP_2007, POP_2008, POP_2009)
) U 
;

/* Format data for years 1997-1999 */
SELECT				B.STATE,
					A.YEAR,
					POP = SUM(A.POP)
INTO				CENSUS_POP_1997_1999					
FROM				JMP_Census_Bureau_Pop_State.dbo.Pop_1997_1999 AS A
				INNER JOIN
					JMP_Census_Bureau_Pop_State.dbo.STATE_FIPS_CROSSWALK_handcollected AS B
				ON	A.FIPS_STATE = B.STATEFIPS
GROUP BY			B.STATE,
					A.YEAR							
;

/* Merge into a single table */
SELECT				*
INTO				CENSUS_POP_CLEANSED
FROM				CENSUS_POP_2010_2015
;

INSERT INTO 		CENSUS_POP_CLEANSED	
SELECT				*
FROM				CENSUS_POP_2000_2009
;	

INSERT INTO 		CENSUS_POP_CLEANSED	
SELECT				*
FROM				CENSUS_POP_1997_1999
;	

/* Take natural log */
ALTER TABLE CENSUS_POP_CLEANSED ADD LN_POPULATION FLOAT;
GO

UPDATE CENSUS_POP_CLEANSED SET LN_POPULATION = LOG(1 + POP);

/* Set data type for [year] as INT */
ALTER TABLE CENSUS_POP_CLEANSED ALTER COLUMN YEAR INT;

/* Drop intermediary tables */
DROP TABLE CENSUS_POP_2010_2015_1;
DROP TABLE CENSUS_POP_2010_2015  ;
DROP TABLE CENSUS_POP_2000_2009_1;
DROP TABLE CENSUS_POP_2000_2009  ;
DROP TABLE CENSUS_POP_1997_1999  ;

/* **************************************************************************************** */
/* Cleansing state-level data from Bureau of Economic Analysis, which I use for the "GDP per capita" variable */	

/* Retain relevant variables */
SELECT				STATE = B.State_abbreviation,
					YR1997 = [1997]             ,
					YR1998 = [1998]             ,
					YR1999 = [1999]             ,
					YR2000 = [2000]             ,
					YR2001 = [2001]             ,
					YR2002 = [2002]             ,
					YR2003 = [2003]             ,
					YR2004 = [2004]             ,
					YR2005 = [2005]             ,
					YR2006 = [2006]             ,
					YR2007 = [2007]             ,
					YR2008 = [2008]             ,
					YR2009 = [2009]             ,
					YR2010 = [2010]             ,
					YR2011 = [2011]             ,
					YR2012 = [2012]             ,
					YR2013 = [2013]             ,
					YR2014 = [2014]             ,
					YR2015 = [2015]
INTO				BEA_GDP_CLEANSED1
FROM				JMP_BEA_GDP_STATE.dbo.GDP_STATE_RAW AS A
				INNER JOIN
					JMP_BEA_GDP_STATE.dbo.STATE_ABBREVIATION_Handcollected AS B
				ON	A.GEONAME = B.STATE_NAME
WHERE				A.INDUSTRYID = '1'	
				AND	A.GEOFIPS <> '00000'
				AND	CONVERT(INT,SUBSTRING(A.GEOFIPS,1,2)) < 90
				AND	A.COMPONENTNAME = 'Per capita real GDP by state'
;

-- Unpivot 
SELECT				STATE                              ,
					YEAR = SUBSTRING(CAPTION,3,4)      ,
					GDP_PER_CAPITA = CONVERT(FLOAT,AMT)
INTO				BEA_GDP_CLEANSED					
FROM				BEA_GDP_CLEANSED1
UNPIVOT
(
AMT FOR CAPTION IN (YR1997, YR1998, YR1999, YR2000, YR2001, YR2002, YR2003, YR2004, YR2005, YR2006, YR2007, YR2008, YR2009, YR2010, YR2011, YR2012, YR2013, YR2014, YR2015)
) U 
;

/* Set data format */
ALTER TABLE BEA_GDP_CLEANSED ALTER COLUMN STATE VARCHAR(2);
ALTER TABLE BEA_GDP_CLEANSED ALTER COLUMN YEAR  INT       ;

/* Drop intermediary table */
DROP TABLE			BEA_GDP_CLEANSED1;

/* **************************************************************************************** */
/* Cleansing county-level data from Bureau of Economic Analysis, which I use for the "GDP per capita" variable */	

/* Retrieve raw GDP */
SELECT				FIPS = GEOFIPS,
					GEONAME       ,
					YR2001        ,
					YR2002        ,
					YR2003        ,
					YR2004        ,
					YR2005        ,
					YR2006        ,
					YR2007        ,
					YR2008        ,
					YR2009        ,
					YR2010        ,
					YR2011        ,
					YR2012        ,
					YR2013        ,
					YR2014        ,
					YR2015        ,
					YR2016        
INTO				BEA_GDP_COUNTY_CLEANSED01
FROM				JMP_BEA_GDP_COUNTY.dbo.GDP_COUNTY_RAW
WHERE				SUBSTRING(GEOFIPS,3,3) <> '000'
				AND	CONVERT(INT,SUBSTRING(GEOFIPS,1,2)) < 90
				AND	DESCRIPTION = 'All industry total'
;			

-- Unpivot 
SELECT				FIPS                         ,
					GEONAME                      ,
					YEAR = SUBSTRING(CAPTION,3,4),
					GDP
INTO				BEA_GDP_COUNTY_CLEANSED02					
FROM				BEA_GDP_COUNTY_CLEANSED01
UNPIVOT
(
GDP FOR CAPTION IN (YR2001, YR2002, YR2003, YR2004, YR2005, YR2006, YR2007, YR2008, YR2009, YR2010, YR2011, YR2012, YR2013, YR2014, YR2015, YR2016)
) U 
;

/* Retrieve popupation  */
SELECT				FIPS = GEOFIPS,
					GEONAME       ,
					YR2001        ,
					YR2002        ,
					YR2003        ,
					YR2004        ,
					YR2005        ,
					YR2006        ,
					YR2007        ,
					YR2008        ,
					YR2009        ,
					YR2010        ,
					YR2011        ,
					YR2012        ,
					YR2013        ,
					YR2014        ,
					YR2015        ,
					YR2016        
INTO				BEA_GDP_COUNTY_CLEANSED03
FROM				JMP_BEA_GDP_COUNTY.dbo.POP_COUNTY_RAW
WHERE				SUBSTRING(GEOFIPS,3,3) <> '000'
				AND	CONVERT(INT,SUBSTRING(GEOFIPS,1,2)) < 90
				AND	UNIT = 'Number of persons'
;		

-- Unpivot
SELECT				FIPS                         ,
					GEONAME                      ,
					YEAR = SUBSTRING(CAPTION,3,4),
					POP
INTO				BEA_GDP_COUNTY_CLEANSED04					
FROM				BEA_GDP_COUNTY_CLEANSED03
UNPIVOT
(
POP FOR CAPTION IN (YR2001, YR2002, YR2003, YR2004, YR2005, YR2006, YR2007, YR2008, YR2009, YR2010, YR2011, YR2012, YR2013, YR2014, YR2015, YR2016)
) U 
;

/* Append and compute GDP per capita */
SELECT				A.*                           ,
					GDP_PER_CAPITA = A.GDP / B.POP
INTO				BEA_GDP_COUNTY_CLEANSED
FROM				BEA_GDP_COUNTY_CLEANSED02 AS A
				INNER JOIN
					BEA_GDP_COUNTY_CLEANSED04 AS B
				ON	A.FIPS = B.FIPS
				AND A.YEAR = B.YEAR
;

/* Set data format */
ALTER TABLE BEA_GDP_COUNTY_CLEANSED ALTER COLUMN YEAR INT;				

/* Drop intermediary tables */
DROP TABLE BEA_GDP_COUNTY_CLEANSED01;
DROP TABLE BEA_GDP_COUNTY_CLEANSED02;
DROP TABLE BEA_GDP_COUNTY_CLEANSED03;
DROP TABLE BEA_GDP_COUNTY_CLEANSED04;

/* **************************************************************************************** */
/* Cleansing data from Bureau of Labor Statistics, which I use for the "Unemployment" variable */	

/* Retrieve unemployment at state-year level */
SELECT				B.STATE                                                                                   ,
					A.YEAR                                                                                    ,
					UNEMPLOYMENT_RATE = SUM(CONVERT(FLOAT, A.UNEMPLOYED)) / SUM(CONVERT(FLOAT, A.LABOR_FORCE))
INTO				BLS_UNEMPLOYMENT_CLEANSED									
FROM				JMP_BLS_Unemployment.dbo.BLS_unemployment_raw AS A
				INNER JOIN
					JMP_BLS_Unemployment.dbo.STATE_FIPS_CROSSWALK_Handcollected AS B
				ON	A.STATE_FIPS = B.STATEFIPS
WHERE				A.LABOR_FORCE	IS NOT NULL		
				AND	A.UNEMPLOYED	IS NOT NULL	
GROUP BY			B.STATE,
					A.YEAR							
;

/* Retrieve unemployment at county-year level */
SELECT				FIPS = STATE_FIPS + COUNTY_FIPS             ,
					YEAR                                        ,
					UNEMPLOYMENT_RATE = UNEMPLOYED / LABOR_FORCE
INTO				BLS_UNEMPLOYMENT_COUNTY_CLEANSED
FROM				JMP_BLS_Unemployment.dbo.BLS_unemployment_raw 
WHERE				LABOR_FORCE	IS NOT NULL		
				AND	UNEMPLOYED	IS NOT NULL				
;

/* **************************************************************************************** */
/* Cleansing Charity Navigator data */

/* For a given charity-year combination, retain only 1 observation */
-- Retain the observation that was published most recently (e.g., Charity Navigator sometimes amends ratings in cases such as when a charity amended its Form 990)
SELECT DISTINCT	A.EIN                                          ,
				FISCAL_YEAR = DATEPART(YEAR,A.PUBLICATION_DATE),
				A.RATING_SCORE_OVERALL                           
INTO			CHARITY_NAVIGATOR_CLEANSED1				
FROM			JMP_CHARITY_NAVIGATOR.DBO.CHARITY_NAVIGATOR AS A
			INNER JOIN
				(
				SELECT			EIN                                       ,
								FISCAL_YEAR = DATEPART(YEAR,PUBLICATION_DATE),
								PUBLICATION_DATE  = MAX(PUBLICATION_DATE)
				FROM			JMP_CHARITY_NAVIGATOR.DBO.CHARITY_NAVIGATOR
				GROUP BY		EIN                         ,
								DATEPART(YEAR,PUBLICATION_DATE)
				) AS B
			ON	A.EIN                          = B.EIN           
			AND	DATEPART(YEAR,A.PUBLICATION_DATE) = B.FISCAL_YEAR
			AND	A.PUBLICATION_DATE                = B.PUBLICATION_DATE 
;

-- If there are still more than observation for for a given charity-year combination, retain the one that appears last in the data
WITH TEMP_CN AS
(
SELECT 			ROW_NO = ROW_NUMBER() OVER (ORDER BY EIN, FISCAL_YEAR),
				*		
FROM			CHARITY_NAVIGATOR_CLEANSED1
)
SELECT			A.*
INTO			CHARITY_NAVIGATOR_CLEANSED
FROM			TEMP_CN AS A
			INNER JOIN
				(					
				SELECT		EIN                 ,
							FISCAL_YEAR         ,
							ROW_NO = MAX(ROW_NO)
				FROM		TEMP_CN
				GROUP BY	EIN        ,
							FISCAL_YEAR		
				) AS B	
			ON	A.ROW_NO = B.ROW_NO
;	

/* Drop intermediary table */
DROP TABLE CHARITY_NAVIGATOR_CLEANSED1;


/* **************************************************************************************** */
/* Section 2: Charity-year sample                                                           */
/* **************************************************************************************** */

/* Merge all data sources  */
-- Create table
CREATE TABLE 		SAMPLE_CHARITY_YEAR_TEMP
					(
					DATASOURCE       	         VARCHAR(20) ,
					DATASOURCE_BREADTH_HIERARCHY INT		 ,
					FISCAL_YEAR      	         INT         ,
					PERIOD_END				     DATE	     ,
					EIN                 	     VARCHAR(10) ,
					ORGNAME             	     VARCHAR(200),
					STATE               	     VARCHAR(5)  ,
					CITY               		     VARCHAR(100),
					NTEE_CODE      	             VARCHAR(5)  ,
					CONTRIB_ALL         	     FLOAT       ,
					REVENUE_TOTAL       	     FLOAT       ,
					ASSETS_TOTAL_EOY    	     FLOAT       ,
					GOV_GRANTS          	     FLOAT       ,
					AUDIT      	                 INT         ,
					PPE_NET             	     FLOAT       ,
					ACCOUNTING_FEES_TOTAL	     FLOAT		 ,
					TOTAL_PROGRAM_SERVICES       FLOAT		 ,
					EXPENSES_TOTAL               FLOAT		 ,
					GROSS_INC				     FLOAT		 ,
					GROUP_FILING			     INT		 ,
					FED_CAMPAIGNS			     FLOAT		 ,
					SCHED_B					     INT		 ,
					POST990                      INT         ,
					SCHED_O_III                  INT
					)
;

-- Add data from Guidestar_post2008
INSERT INTO 		SAMPLE_CHARITY_YEAR_TEMP	
SELECT				DATASOURCE                   = 'GUIDESTAR_POST2008',
					DATASOURCE_BREADTH_HIERARCHY = 1                   ,
					FISCAL_YEAR                                        ,
					PERIOD_END                                         ,
					EIN                                                ,
					ORGNAME                                            ,
					STATE                                              ,
					CITY                                               ,
					NTEE_CODE                                          ,
					CONTRIB_ALL                                        ,
					REVENUE_TOTAL                                      ,
					ASSETS_TOTAL_EOY                                   ,
					GOV_GRANTS                                         ,
					AUDIT                                              ,
					PPE_NET                                            ,
					ACCOUNTING_FEES_TOTAL                              ,
					TOTAL_PROGRAM_SERVICES                             ,
					EXPENSES_TOTAL                                     ,
					GROSS_INC                                          ,
					GROUP_FILING                                       ,
					FED_CAMPAIGNS                                      ,
					SCHED_B	                                           ,
					POST990                                            ,
					SCHED_O_III
FROM				GUIDESTAR_POST2008_CLEANSED
;	

-- Add data from Guidestar_0408
INSERT INTO 		SAMPLE_CHARITY_YEAR_TEMP	
SELECT				DATASOURCE                   = 'GUIDESTAR_0408'        ,
					DATASOURCE_BREADTH_HIERARCHY = 2                       ,
					FISCAL_YEAR                                            ,
					PERIOD_END                                             ,
					EIN                                                    ,
					ORGNAME                                                ,
					STATE                                                  ,
					CITY                                                   ,
					NTEE_CODE                                              ,
					CONTRIB_ALL                                            ,
					REVENUE_TOTAL                                          ,
					ASSETS_TOTAL_EOY                                       ,
					GOV_GRANTS                                             ,
					AUDIT                        = NULL                    ,
					PPE_NET                      = PPE_INVEST_NET + PPE_NET,
					ACCOUNTING_FEES_TOTAL                                  ,
					TOTAL_PROGRAM_SERVICES                                 ,
					EXPENSES_TOTAL                                         ,
					GROSS_INC                                              ,
					GROUP_FILING                                           ,
					FED_CAMPAIGNS                                          ,
					SCHED_B                      = NULL                    ,
					POST990                      = NULL                    ,
					SCHED_O_III                  = NULL
FROM				GUIDESTAR_0408_CLEANSED
;

-- Add data from NCCS_Guidestar
INSERT INTO 		SAMPLE_CHARITY_YEAR_TEMP	
SELECT				DATASOURCE                   = 'NCCS_GUIDESTAR'        ,
					DATASOURCE_BREADTH_HIERARCHY = 3                       ,
					FISCAL_YEAR                                            ,
					PERIOD_END                                             ,
					EIN                                                    ,
					ORGNAME                                                ,
					STATE                                                  ,
					CITY                                                   ,
					NTEE_CODE                                              ,
					CONTRIB_ALL                                            ,
					REVENUE_TOTAL                                          ,
					ASSETS_TOTAL_EOY                                       ,
					GOV_GRANTS                                             ,
					AUDIT                        = NULL                    ,
					PPE_NET                      = PPE_INVEST_NET + PPE_NET,
					ACCOUNTING_FEES_TOTAL                                  ,
					TOTAL_PROGRAM_SERVICES                                 ,
					EXPENSES_TOTAL                                         ,
					GROSS_INC                                              ,
					GROUP_FILING                                           ,
					FED_CAMPAIGNS                                          ,
					SCHED_B					     = NULL                    ,
					POST990				         = NULL                    ,
					SCHED_O_III                  = NULL
FROM				NCCS_GUIDESTAR_CLEANSED
;

-- Add data from Guidestar_EZ
INSERT INTO 		SAMPLE_CHARITY_YEAR_TEMP	
SELECT				DATASOURCE                   = 'GUIDESTAR_EZ',
					DATASOURCE_BREADTH_HIERARCHY = 4             ,
					FISCAL_YEAR                                  ,
					PERIOD_END                                   ,
					EIN                                          ,
					ORGNAME                                      ,
					STATE                                        ,
					CITY                                         ,
					NTEE_CODE                                    ,
					CONTRIB_ALL                                  ,
					REVENUE_TOTAL                                ,
					ASSETS_TOTAL_EOY                             ,
					GOV_GRANTS                   = NULL          ,
					AUDIT                        = NULL          ,
					PPE_NET                      = NULL          ,
					ACCOUNTING_FEES_TOTAL        = NULL          ,
					TOTAL_PROGRAM_SERVICES                       ,
					EXPENSES_TOTAL                               ,
					GROSS_INC                                    ,
					GROUP_FILING                 = NULL          ,
					FED_CAMPAIGNS			     = NULL          ,
					SCHED_B					     = NULL          ,
					POST990				         = NULL          ,
					SCHED_O_III                  = NULL	
FROM				GUIDESTAR_EZ_CLEANSED
;	

-- Add data from NCCS_CORE  
INSERT INTO 		SAMPLE_CHARITY_YEAR_TEMP
SELECT 				DATASOURCE                   = 'NCCS_CORE',
					DATASOURCE_BREADTH_HIERARCHY = 5          ,
					FISCAL_YEAR                               ,
					PERIOD_END                                ,
					EIN                                       ,
					ORGNAME                                   ,
					STATE                                     ,
					CITY                                      ,
					NTEE_CODE                                 ,
					CONTRIB_ALL                               ,
					REVENUE_TOTAL                             ,
					ASSETS_TOTAL_EOY                          ,
					GOV_GRANTS 			         = NULL       ,
					AUDIT 	                     = NULL       ,
					PPE_NET           	         = NULL       ,
					ACCOUNTING_FEES_TOTAL        = NULL       ,
					TOTAL_PROGRAM_SERVICES       = NULL       ,
					EXPENSES_TOTAL 		         = NULL       ,
					GROSS_INC			                      ,
					GROUP_FILING		         = NULL       ,
					FED_CAMPAIGNS		         = NULL       ,
					SCHED_B				         = NULL       ,
					POST990				         = NULL       ,
					SCHED_O_III                  = NULL
FROM				NCCS_CORE_PC_CLEANSED
;

-- Add data from NCCS_BMF 
INSERT INTO 		SAMPLE_CHARITY_YEAR_TEMP
SELECT 				DATASOURCE                    = 'NCCS_BMF',
					DATASOURCE_BREADTH_HIERARCHY  = NULL      ,
					FISCAL_YEAR                               ,
					PERIOD_END                                ,
					EIN                                       ,
					ORGNAME                       = NULL      ,
					STATE                                     ,
					CITY                                      ,
					NTEE_CODE                                 ,
					CONTRIB_ALL                   = NULL      ,
					REVENUE_TOTAL                 = NULL      ,
					ASSETS_TOTAL_EOY              = NULL      ,
					GOV_GRANTS 			          = NULL      ,
					AUDIT 	                      = NULL      ,
					PPE_NET           	          = NULL      ,
					ACCOUNTING_FEES_TOTAL         = NULL      ,
					TOTAL_PROGRAM_SERVICES        = NULL      ,
					EXPENSES_TOTAL 		          = NULL      ,
					GROSS_INC			          = NULL      ,
					GROUP_FILING		          = NULL      ,
					FED_CAMPAIGNS		          = NULL      ,
					SCHED_B				          = NULL      ,
					POST990				          = NULL      ,
					SCHED_O_III                   = NULL
FROM				NCCS_BMF_CLEANSED
WHERE				LEVEL1 = 'PC'				
;

/* Drop charity-year observations with gross receipts under 50K as these are not required to file any financial information */
DELETE FROM SAMPLE_CHARITY_YEAR_TEMP WHERE GROSS_INC < 50000;

/* For each charity-year combination, retain the charity-year observation from the data source that covers the most data points (e.g., the guidestar data contains more data points than the NCCS data) */
SELECT				A.*
INTO				SAMPLE_CHARITY_YEAR
FROM				SAMPLE_CHARITY_YEAR_TEMP AS A
				INNER JOIN
					(
					SELECT		EIN                                                             ,
								FISCAL_YEAR                                                     ,
								DATASOURCE_BREADTH_HIERARCHY = MIN(DATASOURCE_BREADTH_HIERARCHY) 
					FROM		SAMPLE_CHARITY_YEAR_TEMP
					GROUP BY	EIN,
								FISCAL_YEAR
					) AS B 
				ON	A.EIN                          = B.EIN
				AND	A.FISCAL_YEAR                  = B.FISCAL_YEAR
				AND	A.DATASOURCE_BREADTH_HIERARCHY = B.DATASOURCE_BREADTH_HIERARCHY
;				

/* ************************************************************ */
/* NTEE code: For a given NTEE, retrieve the latest NTEE from the NCCS data. If missing, use the Guidestar data. */

-- Retrieve most recent NTEE for a given charity based on the NCCS data (i.e., the broadest data source because it includes registration information on charities)
SELECT DISTINCT		A.EIN,
					NTEE_CODE =
				CASE
					WHEN C.NTEE_CODE IS NOT NULL THEN C.NTEE_CODE
					ELSE B.NTEE_CODE
				END
INTO 				TEMP_NTEE					
FROM               (				
					SELECT			EIN                          ,
									FISCAL_YEAR = MAX(FISCAL_YEAR)
					FROM			SAMPLE_CHARITY_YEAR_TEMP
					WHERE			NTEE_CODE IS NOT NULL 
								AND	(
									DATASOURCE = 'NCCS_BMF'
								OR	DATASOURCE = 'NCCS_CORE'
									)
					GROUP BY 		EIN
					) AS A
				LEFT OUTER JOIN 
					(				
					SELECT			EIN        ,
									FISCAL_YEAR,
									NTEE_CODE
					FROM			SAMPLE_CHARITY_YEAR_TEMP
					WHERE			NTEE_CODE  IS NOT NULL 
								AND	DATASOURCE = 'NCCS_BMF'
					) AS B
				ON	A.EIN         = B.EIN
				AND	A.FISCAL_YEAR = B.FISCAL_YEAR	
				LEFT OUTER JOIN 
					(				
					SELECT			EIN        ,
									FISCAL_YEAR,
									NTEE_CODE
					FROM			SAMPLE_CHARITY_YEAR_TEMP
					WHERE			NTEE_CODE  IS NOT NULL 
								AND	DATASOURCE = 'NCCS_CORE'
					) AS C
				ON	A.EIN         = C.EIN
				AND	A.FISCAL_YEAR = C.FISCAL_YEAR					
;		

-- If, for a given charity, the NTEE is missing from NCCS, retrieve from Guidestar 
INSERT INTO 		TEMP_NTEE
SELECT DISTINCT		A.EIN      ,
					A.NTEE_CODE		
FROM				SAMPLE_CHARITY_YEAR_TEMP AS A
				INNER JOIN
					(
					SELECT 			EIN                           ,
									FISCAL_YEAR = MAX(FISCAL_YEAR) 
					FROM			SAMPLE_CHARITY_YEAR_TEMP
					WHERE			NTEE_CODE IS NOT NULL 
					GROUP BY		EIN
					) AS B 
				ON	A.EIN = B.EIN
				AND	A.FISCAL_YEAR = B.FISCAL_YEAR
				
				INNER JOIN
					(
					SELECT 			EIN                                                              ,
									FISCAL_YEAR                                                      ,
									MIN(DATASOURCE_BREADTH_HIERARCHY) AS DATASOURCE_BREADTH_HIERARCHY
					FROM			SAMPLE_CHARITY_YEAR_TEMP
					WHERE			NTEE_CODE IS NOT NULL 
					GROUP BY		EIN        ,
									FISCAL_YEAR
					) AS C
				ON	B.EIN                          = C.EIN
				AND	B.FISCAL_YEAR                  = C.FISCAL_YEAR
				AND	A.DATASOURCE_BREADTH_HIERARCHY = C.DATASOURCE_BREADTH_HIERARCHY
				
				LEFT OUTER JOIN				
					TEMP_NTEE AS D	
				ON	A.EIN = D.EIN
WHERE				A.NTEE_CODE IS NOT NULL 	
				AND	D.EIN       IS NULL 									
;				

UPDATE				A SET A.NTEE_CODE = B.NTEE_CODE
FROM				SAMPLE_CHARITY_YEAR AS A
				LEFT OUTER JOIN 
					TEMP_NTEE AS B
				ON	A.EIN = B.EIN
;				

-- Drop intermediary table
DROP TABLE TEMP_NTEE;

/* Drop charity-year observations with missing information or impossible values */
DELETE
FROM				SAMPLE_CHARITY_YEAR
WHERE				NTEE_CODE IS NULL
				OR	CONTRIB_ALL            < 0
				OR	REVENUE_TOTAL          < 0
				OR	ASSETS_TOTAL_EOY       < 0
				OR	GOV_GRANTS             < 0
				OR	FISCAL_YEAR            > 2016
				OR	ACCOUNTING_FEES_TOTAL  < 0
				OR	TOTAL_PROGRAM_SERVICES < 0
				OR	EXPENSES_TOTAL         < 0
				OR  (GOV_GRANTS IS NULL AND REVENUE_TOTAL >= 200000) /* cases where GOV_GRANTS is missing despite revenue being above 200K and long Form 990 being required */
				OR	REVENUE_TOTAL - GOV_GRANTS < 0
				OR  CONTRIB_ALL   - GOV_GRANTS < 0
				OR	STATE IS NULL
				OR 	STATE NOT IN ('WY', 'ND', 'DE', 'SD', 'AK', 'ID', 'NV', 'VT', 'HI', 'UT', 'RI', 'MT', 'MS', 'WV', 'NH', 'NM', 'ME', 'NE', 'AR', 'KS', 'OK', 'LA', 'KY', 'SC', 'AL', 'DC', 'IA', 'AZ', 'CT', 'OR', 'TN', 'MO', 'CO', 'IN', 'MD', 'WI', 'GA', 'WA', 'MN', 'NC', 'VA', 'MI', 'NJ', 'MA', 'IL', 'OH', 'FL', 'PA', 'TX', 'NY', 'CA')
;

/* Compute MandatoryAudit
   i.e. a binary variable equal to 1 if the charity is required to obtain an audit by the audit mandate in its state in that year */
   
-- Compute revenue and contributions net of government grants (i.e., audit mandates stipulate that government grants should be excluded from the calculation)
ALTER TABLE 	SAMPLE_CHARITY_YEAR ADD	RD_REV_LESS_GRANTS     FLOAT,
									    RD_CONTRIB_LESS_GRANTS FLOAT
;
GO

UPDATE			SAMPLE_CHARITY_YEAR SET RD_REV_LESS_GRANTS     = REVENUE_TOTAL - GOV_GRANTS,
								        RD_CONTRIB_LESS_GRANTS = CONTRIB_ALL   - GOV_GRANTS
;

-- Computer average total revenue for the past 3 years (required for WA audit mandate)
ALTER TABLE 	SAMPLE_CHARITY_YEAR ADD	REVENUE_T_MIN_1 FLOAT,
									    REVENUE_T_MIN_2 FLOAT,
									    RD_AVG_REV_3YR  FLOAT
;
GO

UPDATE			A SET A.REVENUE_T_MIN_1 = B.REVENUE_TOTAL, 
					  A.REVENUE_T_MIN_2 = C.REVENUE_TOTAL 
FROM			SAMPLE_CHARITY_YEAR AS A
			LEFT OUTER JOIN
				SAMPLE_CHARITY_YEAR AS B
			ON	A.EIN = B.EIN
			AND	A.FISCAL_YEAR = B.FISCAL_YEAR + 1
			LEFT OUTER JOIN
				SAMPLE_CHARITY_YEAR AS C
			ON	A.EIN = C.EIN
			AND	A.FISCAL_YEAR = C.FISCAL_YEAR + 2
;						  
			  
UPDATE			SAMPLE_CHARITY_YEAR SET RD_AVG_REV_3YR =
												CASE
													WHEN 	REVENUE_T_MIN_1 IS NOT NULL 
														AND	REVENUE_T_MIN_2 IS NOT NULL
													THEN	(REVENUE_TOTAL + REVENUE_T_MIN_1 + REVENUE_T_MIN_2)	/ 3
													
													WHEN 	REVENUE_T_MIN_1 IS NOT NULL 
														AND	REVENUE_T_MIN_2 IS NULL
													THEN	(REVENUE_TOTAL + REVENUE_T_MIN_1) / 2	
									
													WHEN 	REVENUE_T_MIN_1 IS NULL 
														AND	REVENUE_T_MIN_2 IS NOT NULL
													THEN	(REVENUE_TOTAL + REVENUE_T_MIN_2) / 2	
									
													WHEN 	REVENUE_T_MIN_1 IS NULL 
														AND	REVENUE_T_MIN_2 IS NULL
													THEN	REVENUE_TOTAL				
												END
;

ALTER TABLE SAMPLE_CHARITY_YEAR DROP COLUMN REVENUE_T_MIN_1;
ALTER TABLE SAMPLE_CHARITY_YEAR DROP COLUMN REVENUE_T_MIN_2;

-- Compute MandatoryAudit: 1 if revenue measure for audit mandate in that state at that time is above threshold
ALTER TABLE 	SAMPLE_CHARITY_YEAR ADD RD_AUDIT_REQ FLOAT;
GO

UPDATE			A SET A.RD_AUDIT_REQ = 
							CASE
								WHEN A.REVENUE_TOTAL 			> B.REVENUE_TOTAL_CUTOFF 		THEN 1
								WHEN A.RD_REV_LESS_GRANTS		> B.REV_LESS_GRANTS_CUTOFF 		THEN 1
								WHEN A.RD_AVG_REV_3YR			> B.AVG_REV_3YR_CUTOFF 			THEN 1
								WHEN A.RD_CONTRIB_LESS_GRANTS 	> B.CONTRIB_LESS_GRANTS_CUTOFF 	THEN 1
								ELSE 0
							END						
FROM						SAMPLE_CHARITY_YEAR AS A 
						LEFT OUTER JOIN
							JMP_AuditRegulations_Handcollected.DBO.STATE_AUDITREG_HANDCOLLECTED AS B
						ON	A.STATE      = B.STATE	
						AND	A.PERIOD_END > B.DATE_EFFECTIVE
						AND A.PERIOD_END < B.DATE_INEFFECTIVE
;			

/* Create SOCIALNEED, ACTIVITY, MAJORGROUP, CATEGORY variables.  
   These variables are based on the NTEE code and capture the equivalent of the industry in the nonprofit sector at different levels of granularity. 
   SOCIALNEED, ACTIVITY, and MAJORGROUP are defined as the 3-digit, 2-digit, and 1-digit NTEE code respectively.
   CATEGORY is the most aggregate classification. I follow the mapping provided by the NTEE to compute it (Urban Institute 2017).
*/
ALTER TABLE 	SAMPLE_CHARITY_YEAR ADD	SOCIALNEED VARCHAR(3),
									    ACTIVITY   VARCHAR(2),
									    MAJORGROUP VARCHAR(1),
									    CATEGORY   VARCHAR(4)
;
GO

UPDATE			SAMPLE_CHARITY_YEAR	SET SOCIALNEED = SUBSTRING(NTEE_CODE,1,3),
									    ACTIVITY   = SUBSTRING(NTEE_CODE,1,2),
									    MAJORGROUP = SUBSTRING(NTEE_CODE,1,1),
									    CATEGORY   =  
								CASE
									WHEN SUBSTRING(NTEE_CODE,1,1) = 'A' 
									THEN 'I'
									WHEN SUBSTRING(NTEE_CODE,1,1) = 'B' 
									THEN 'II'
									WHEN SUBSTRING(NTEE_CODE,1,1) IN ('C','D') 
									THEN 'III'
									WHEN SUBSTRING(NTEE_CODE,1,1) IN ('E', 'F', 'G', 'H') 
									THEN 'IV'
									WHEN SUBSTRING(NTEE_CODE,1,1) IN ('I', 'J', 'K', 'L', 'M', 'N', 'O', 'P') 
									THEN 'V'
									WHEN SUBSTRING(NTEE_CODE,1,1) = 'Q'
									THEN 'VI'
									WHEN SUBSTRING(NTEE_CODE,1,1) IN ('R', 'S', 'T', 'U', 'V', 'W')
									THEN 'VII'
									WHEN SUBSTRING(NTEE_CODE,1,1) = 'X'
									THEN 'VIII'
									WHEN SUBSTRING(NTEE_CODE,1,1) = 'Y'
									THEN 'IX'
									WHEN SUBSTRING(NTEE_CODE,1,1) = 'Z'
									THEN 'X'					
								END
;

ALTER TABLE 	SAMPLE_CHARITY_YEAR DROP COLUMN NTEE_CODE;

/* Add FIPS : i.e. the unique identifier for each county 
   Use BMF to retrieve FIPS. If the FIPS is missing in BMF, retrieve it from NCCS_CORE if available. 
   In situations where a given charity is missing FIPS data in a given year, use the latest FIPS data available for that charity.
*/

-- Create a list of charity-year-FIPS based on BMF
SELECT			EIN        ,
                FISCAL_YEAR,
				FIPS
INTO			TEMP_FIPS
FROM			NCCS_BMF_CLEANSED
WHERE			FIPS IS NOT NULL 
;						

-- Make the list more comprehensive by adding any charity-year-FIPS that exist in NCCS_CORE, but not in BMF
INSERT INTO		TEMP_FIPS
SELECT			A.EIN        ,
				A.FISCAL_YEAR,
				A.FIPS
FROM			
				(
				SELECT		EIN        ,
                            FISCAL_YEAR,
                            FIPS           
				FROM		NCCS_CORE_PC_CLEANSED
				WHERE		FIPS IS NOT NULL 
				) AS A
			LEFT OUTER JOIN
				TEMP_FIPS AS B
			ON	A.EIN = B.EIN
			AND	A.FISCAL_YEAR = B.FISCAL_YEAR
WHERE			B.FISCAL_YEAR IS NULL				
;						

-- For each EIN, identify latest year for which there is FIPS data
SELECT			A.EIN,
				A.FIPS
INTO			TEMP_FIPS2						
FROM			TEMP_FIPS AS A
			INNER JOIN
				(
				SELECT		EIN,
							FISCAL_YEAR = MAX(FISCAL_YEAR)  
				FROM		TEMP_FIPS
				GROUP BY	EIN
				) AS B
			ON	A.EIN		  = B.EIN
			AND	A.FISCAL_YEAR = B.FISCAL_YEAR
;					

-- Append FIPS. (Use the FIPS for that given charity-year. If it FIPS is missing, use the most recent FIPS available for the given charity) 
ALTER TABLE SAMPLE_CHARITY_YEAR ADD TEMP_FIPS VARCHAR(5);
GO

UPDATE		A SET A.TEMP_FIPS =
					CASE
						WHEN B.FIPS IS NOT NULL 
						THEN B.FIPS
						
						WHEN B.FIPS IS NULL  AND C.FIPS IS NOT NULL 
						THEN C.FIPS
						
						ELSE NULL
					END 
FROM		SAMPLE_CHARITY_YEAR AS A
		LEFT OUTER JOIN
			TEMP_FIPS AS B
		ON	A.EIN         = B.EIN
		AND	A.FISCAL_YEAR = B.FISCAL_YEAR
		LEFT OUTER JOIN
			TEMP_FIPS2 AS C
		ON	A.EIN         = C.EIN
;

-- Require that FIPS be in a state covered by STATE_FIPS_CROSSWALK
ALTER TABLE SAMPLE_CHARITY_YEAR ADD FIPS VARCHAR(5);
GO

UPDATE		A SET A.FIPS =
					CASE
						WHEN TEMP_FIPS IS NOT NULL AND B.STATE = A.STATE THEN TEMP_FIPS
						ELSE NULL
					END 
FROM		SAMPLE_CHARITY_YEAR AS A
		LEFT OUTER JOIN 
			STATE_FIPS_CROSSWALK AS B
		ON	SUBSTRING(A.TEMP_FIPS,1,2) = B.STATEFIPS
;		

ALTER TABLE SAMPLE_CHARITY_YEAR DROP COLUMN TEMP_FIPS;

DROP TABLE TEMP_FIPS;
DROP TABLE TEMP_FIPS2;

/* Compute AGE as difference between the current year and earliest ruling year for a given charity.  
   (I use the BMF and core files as it the broadest source of information on nonprofit regristration.) 
*/
ALTER TABLE		SAMPLE_CHARITY_YEAR ADD AGE FLOAT;
GO 

WITH TEMP_AGE AS 
(
SELECT			EIN                     ,
				RULEDATE = MIN(RULEDATE)
FROM			
				(
				SELECT DISTINCT	EIN     ,
								RULEDATE
				FROM			NCCS_BMF_CLEANSED
				UNION
				SELECT DISTINCT	EIN     ,
								RULEDATE
				FROM			NCCS_CORE_PC_CLEANSED
				) AS A
GROUP BY		EIN
)
UPDATE			A SET A.AGE = CASE WHEN (FISCAL_YEAR - RULEDATE) >= 0 THEN CONVERT(FLOAT,(FISCAL_YEAR - RULEDATE)) ELSE NULL END
FROM			SAMPLE_CHARITY_YEAR AS A
			LEFT OUTER JOIN
				TEMP_AGE AS B
			ON	A.EIN = B.EIN
;

/* Ratings data:
- Create a binary variable equal to 1 if the charity-year observation received a rating
- Append the rating received for a given charity-year observation
 */
ALTER TABLE		SAMPLE_CHARITY_YEAR ADD CN_RATED        FLOAT,
                                        CN_RATING_SCORE FLOAT
;
GO 

UPDATE			A SET A.CN_RATED = CASE WHEN B.EIN IS NOT NULL AND A.FISCAL_YEAR >= 2003 THEN 1 ELSE 0 END,
					  A.CN_RATING_SCORE = B.RATING_SCORE_OVERALL
FROM			SAMPLE_CHARITY_YEAR AS A
			LEFT OUTER JOIN
				CHARITY_NAVIGATOR_CLEANSED AS B
			ON	A.EIN         = B.EIN
			AND A.FISCAL_YEAR = B.FISCAL_YEAR
;

/* Windorize donations */
ALTER TABLE SAMPLE_CHARITY_YEAR ADD CONTRIB_ALL_NOTWINSORIZED FLOAT;
GO

UPDATE SAMPLE_CHARITY_YEAR SET CONTRIB_ALL_NOTWINSORIZED = CONTRIB_ALL;


ALTER TABLE SAMPLE_CHARITY_YEAR ADD CONTRIB_ALL_W FLOAT;
GO

WITH TEMP_WIN AS
(
SELECT		EIN           ,
			FISCAL_YEAR,
			CONTRIB_ALL_W = 
					CASE 
						WHEN CONTRIB_ALL > PERCENTILE_DISC(0.99) WITHIN GROUP (ORDER BY CONTRIB_ALL) OVER () 
						THEN               PERCENTILE_DISC(0.99) WITHIN GROUP (ORDER BY CONTRIB_ALL) OVER () 
																					
						WHEN CONTRIB_ALL < PERCENTILE_DISC(0.01) WITHIN GROUP (ORDER BY CONTRIB_ALL) OVER ()
						THEN               PERCENTILE_DISC(0.01) WITHIN GROUP (ORDER BY CONTRIB_ALL) OVER ()
						
						ELSE CONTRIB_ALL 
					END 
FROM		SAMPLE_CHARITY_YEAR			
)
UPDATE 		A SET A.CONTRIB_ALL_W = B.CONTRIB_ALL_W
FROM		SAMPLE_CHARITY_YEAR AS A
		LEFT OUTER JOIN
			TEMP_WIN AS B
		ON	A.EIN         = B.EIN
		AND	A.FISCAL_YEAR = B.FISCAL_YEAR
;


/* Create a binary variable to identify exempt organizations and those excluded by the sample selection criteira. That is: 
- charities in education, health, mutual benefit organizations 
- charities specifically exempt in certain states
- Charities whose NTEE code is unknown (i.e., NTEE = Z) 
*/
ALTER TABLE		SAMPLE_CHARITY_YEAR ADD 
			EXEMPT AS
		CASE
			WHEN 		MAJORGROUP <>'B'
				AND		MAJORGROUP <>'E'
				AND		MAJORGROUP <>'Y'
				AND		MAJORGROUP <>'Z'
				AND NOT	(STATE = 'FL' AND ACTIVITY ='W3')
				AND NOT	(STATE = 'IL' AND ACTIVITY ='W3')
				AND NOT	(STATE = 'IL' AND ACTIVITY ='O2')
				AND NOT	(STATE = 'KS' AND ACTIVITY ='O2')
				AND NOT	(STATE = 'MA' AND ACTIVITY ='W3')
				AND NOT	(STATE = 'NJ' AND ACTIVITY ='W3')
				AND NOT	(STATE = 'RI' AND ACTIVITY ='W3')
				AND NOT	(STATE = 'WI' AND ACTIVITY ='W3')
			THEN 0		
			ELSE 1
		END
;	
GO

/* Information asymmetry variables */
ALTER TABLE		SAMPLE_CHARITY_YEAR ADD LOW_IA  AS CASE WHEN ACTIVITY IN ('S8', 'Y4', 'N0', 'N1', 'N3', 'N5', 'N6', 'N7', 'N9', 'O1', 'O2', 'O3', 'O4', 'O9') THEN 1 ELSE 0 END,
										HIGH_IA AS CASE WHEN ACTIVITY IN ('Q1', 'Q2', 'Q3', 'Q7', 'P8', 'F2', 'F5'                                          ) THEN 1 ELSE 0 END
;	
GO

/* Drop intermediary table */
DROP TABLE SAMPLE_CHARITY_YEAR_TEMP;

/* ************************************************************ */
/* Create unique identifiers for each fixed effect combination */

/* Create an intermediary table for each identifier */
SELECT 					ROW_NUMBER() 
OVER					(ORDER BY AA.EIN) AS EIN_ID, 
						*
INTO					TEMP_EIN_ID						
FROM							
						(
						SELECT DISTINCT	EIN
						FROM			SAMPLE_CHARITY_YEAR
						) AS AA						
;

SELECT 					ROW_NUMBER() 
OVER					(ORDER BY AA.CATEGORY) AS CATEGORY_ID, 
						*
INTO					TEMP_CATEGORY_ID		
FROM							
						(
						SELECT DISTINCT	CATEGORY
						FROM			SAMPLE_CHARITY_YEAR
						) AS AA						
;

SELECT 					ROW_NUMBER() 
OVER					(ORDER BY AA.MAJORGROUP) AS MAJORGROUP_ID, 
						*
INTO					TEMP_MAJORGROUP_ID		
FROM							
						(
						SELECT DISTINCT	MAJORGROUP
						FROM			SAMPLE_CHARITY_YEAR
						) AS AA						
;

SELECT 					ROW_NUMBER() 
OVER					(ORDER BY AA.ACTIVITY) AS ACTIVITY_ID, 
						*
INTO					TEMP_ACTIVITY_ID		
FROM							
						(
						SELECT DISTINCT	ACTIVITY
						FROM			SAMPLE_CHARITY_YEAR
						) AS AA						
;


SELECT 					ROW_NUMBER() 
OVER					(ORDER BY AA.SOCIALNEED) AS SOCIALNEED_ID, 
						*
INTO					TEMP_SOCIALNEED_ID		
FROM							
						(
						SELECT DISTINCT	SOCIALNEED
						FROM			SAMPLE_CHARITY_YEAR
						) AS AA						
;

SELECT 					ROW_NUMBER() 
OVER					(ORDER BY AA.STATE) AS STATE_ID, 
						*
INTO					TEMP_STATE_ID
FROM							
						(
						SELECT DISTINCT	STATE
						FROM			SAMPLE_CHARITY_YEAR
						) AS AA		
;

SELECT 					ROW_NUMBER() 
OVER					(ORDER BY AA.STATE) AS STATE_YEAR_ID, 
						*
INTO					TEMP_STATE_YEAR_ID
FROM							
						(
						SELECT DISTINCT	AAA.STATE      ,
										BBB.FISCAL_YEAR
						FROM			SAMPLE_CHARITY_YEAR AS AAA,
										SAMPLE_CHARITY_YEAR AS BBB
						) AS AA		
;

SELECT 					ROW_NUMBER() 
OVER					(ORDER BY AA.CATEGORY) AS CATEGORY_YEAR_ID, 
						*
INTO					TEMP_CATEGORY_YEAR_ID
FROM							
						(
						SELECT DISTINCT	AAA.CATEGORY   ,
										BBB.FISCAL_YEAR
						FROM			SAMPLE_CHARITY_YEAR AS AAA,
										SAMPLE_CHARITY_YEAR AS BBB
						) AS AA		
;

SELECT 					ROW_NUMBER() 
OVER					(ORDER BY AA.MAJORGROUP) AS MAJORGROUP_YEAR_ID, 
						*
INTO					TEMP_MAJORGROUP_YEAR_ID
FROM							
						(
						SELECT DISTINCT	AAA.MAJORGROUP ,
										BBB.FISCAL_YEAR
						FROM			SAMPLE_CHARITY_YEAR AS AAA,
										SAMPLE_CHARITY_YEAR AS BBB
						) AS AA		
;

SELECT 					ROW_NUMBER() 
OVER					(ORDER BY AA.ACTIVITY) AS ACTIVITY_YEAR_ID, 
						*
INTO					TEMP_ACTIVITY_YEAR_ID
FROM							
						(
						SELECT DISTINCT	AAA.ACTIVITY   ,
										BBB.FISCAL_YEAR
						FROM			SAMPLE_CHARITY_YEAR AS AAA,
										SAMPLE_CHARITY_YEAR AS BBB
						) AS AA		
;

SELECT 					ROW_NUMBER() 
OVER					(ORDER BY AA.SOCIALNEED) AS SOCIALNEED_YEAR_ID, 
						*
INTO					TEMP_SOCIALNEED_YEAR_ID
FROM							
						(
						SELECT DISTINCT	AAA.SOCIALNEED ,
										BBB.FISCAL_YEAR
						FROM			SAMPLE_CHARITY_YEAR AS AAA,
										SAMPLE_CHARITY_YEAR AS BBB
						) AS AA		
;
	
SELECT 					ROW_NUMBER() 
OVER					(ORDER BY AA.STATE) AS STATE_CATEGORY_ID, 
						*
INTO					TEMP_STATE_CATEGORY_ID
FROM							
						(
						SELECT DISTINCT	AAA.STATE   ,
										BBB.CATEGORY
						FROM			SAMPLE_CHARITY_YEAR AS AAA,
										SAMPLE_CHARITY_YEAR AS BBB
						) AS AA	
;	

SELECT 					ROW_NUMBER() 
OVER					(ORDER BY AA.STATE) AS STATE_MAJORGROUP_ID, 
						*
INTO					TEMP_STATE_MAJORGROUP_ID
FROM							
						(
						SELECT DISTINCT	AAA.STATE     ,
										BBB.MAJORGROUP
						FROM			SAMPLE_CHARITY_YEAR AS AAA,
										SAMPLE_CHARITY_YEAR AS BBB
						) AS AA	
;				

SELECT 					ROW_NUMBER() 
OVER					(ORDER BY AA.STATE) AS STATE_ACTIVITY_ID, 
						*
INTO					TEMP_STATE_ACTIVITY_ID
FROM							
						(
						SELECT DISTINCT	AAA.STATE   ,
										BBB.ACTIVITY
						FROM			SAMPLE_CHARITY_YEAR AS AAA,
										SAMPLE_CHARITY_YEAR AS BBB
						) AS AA	
;						

SELECT 					ROW_NUMBER() 
OVER					(ORDER BY AA.STATE) AS STATE_SOCIALNEED_ID, 
						*
INTO					TEMP_STATE_SOCIALNEED_ID
FROM							
						(
						SELECT DISTINCT	AAA.STATE     ,
										BBB.SOCIALNEED
						FROM			SAMPLE_CHARITY_YEAR AS AAA,
										SAMPLE_CHARITY_YEAR AS BBB
						) AS AA	
;		

SELECT 					ROW_NUMBER() 
OVER					(ORDER BY AA.CATEGORY) AS STATE_CATEGORY_YEAR_ID, 
						*
INTO					TEMP_STATE_CATEGORY_YEAR_ID
FROM							
						(
						SELECT DISTINCT	AAA.*,
										BBB.*,
										CCC.*
						FROM			(
										SELECT DISTINCT	CATEGORY
										FROM			SAMPLE_CHARITY_YEAR
										) AS AAA,
										(
										SELECT DISTINCT	FISCAL_YEAR
										FROM			SAMPLE_CHARITY_YEAR
										) AS BBB,
										(
										SELECT DISTINCT	STATE
										FROM			SAMPLE_CHARITY_YEAR
										) AS CCC
						) AS AA						
;

SELECT 					ROW_NUMBER() 
OVER					(ORDER BY AA.MAJORGROUP) AS STATE_MAJORGROUP_YEAR_ID, 
						*
INTO					TEMP_STATE_MAJORGROUP_YEAR_ID
FROM							
						(
						SELECT DISTINCT	AAA.*,
										BBB.*,
										CCC.*
						FROM			(
										SELECT DISTINCT	MAJORGROUP
										FROM			SAMPLE_CHARITY_YEAR
										) AS AAA,
										(
										SELECT DISTINCT	FISCAL_YEAR
										FROM			SAMPLE_CHARITY_YEAR
										) AS BBB,
										(
										SELECT DISTINCT	STATE
										FROM			SAMPLE_CHARITY_YEAR
										) AS CCC
						) AS AA						
;

SELECT 					ROW_NUMBER() 
OVER					(ORDER BY AA.ACTIVITY) AS STATE_ACTIVITY_YEAR_ID, 
						*
INTO					TEMP_STATE_ACTIVITY_YEAR_ID
FROM							
						(
						SELECT DISTINCT	AAA.*,
										BBB.*,
										CCC.*
						FROM			(
										SELECT DISTINCT	ACTIVITY
										FROM			SAMPLE_CHARITY_YEAR
										) AS AAA,
										(
										SELECT DISTINCT	FISCAL_YEAR
										FROM			SAMPLE_CHARITY_YEAR
										) AS BBB,
										(
										SELECT DISTINCT	STATE
										FROM			SAMPLE_CHARITY_YEAR
										) AS CCC
						) AS AA						
;

SELECT 					ROW_NUMBER() 
OVER					(ORDER BY AA.SOCIALNEED) AS STATE_SOCIALNEED_YEAR_ID, 
						*
INTO					TEMP_STATE_SOCIALNEED_YEAR_ID
FROM							
						(
						SELECT DISTINCT	AAA.*,
										BBB.*,
										CCC.*
						FROM			(
										SELECT DISTINCT	SOCIALNEED
										FROM			SAMPLE_CHARITY_YEAR
										) AS AAA,
										(
										SELECT DISTINCT	FISCAL_YEAR
										FROM			SAMPLE_CHARITY_YEAR
										) AS BBB,
										(
										SELECT DISTINCT	STATE
										FROM			SAMPLE_CHARITY_YEAR
										) AS CCC
						) AS AA						
;

SELECT 					ROW_NUMBER() 
OVER					(ORDER BY AA.FIPS) AS FIPS_ID, 
						*
INTO					TEMP_FIPS_ID
FROM							
						(
						SELECT DISTINCT	FIPS
						FROM			SAMPLE_CHARITY_YEAR
						) AS AA		
;

SELECT 					ROW_NUMBER() 
OVER					(ORDER BY AA.FIPS) AS FIPS_YEAR_ID, 
						*
INTO					TEMP_FIPS_YEAR_ID
FROM							
						(
						SELECT DISTINCT	AAA.FIPS       ,
										BBB.FISCAL_YEAR
						FROM			SAMPLE_CHARITY_YEAR AS AAA,
										SAMPLE_CHARITY_YEAR AS BBB
						) AS AA		
;

/* Append the identifiers */
ALTER TABLE SAMPLE_CHARITY_YEAR ADD EIN_ID                   INT,
							        CATEGORY_ID              INT,
                                    MAJORGROUP_ID            INT,
                                    ACTIVITY_ID              INT,
                                    SOCIALNEED_ID            INT,
                                    STATE_ID                 INT,
                                    STATE_YEAR_ID            INT,
                                    CATEGORY_YEAR_ID         INT,
                                    MAJORGROUP_YEAR_ID       INT,
                                    ACTIVITY_YEAR_ID         INT,
                                    SOCIALNEED_YEAR_ID       INT,
                                    STATE_CATEGORY_ID        INT,
                                    STATE_MAJORGROUP_ID      INT,
                                    STATE_ACTIVITY_ID        INT,
                                    STATE_SOCIALNEED_ID      INT,
                                    STATE_CATEGORY_YEAR_ID   INT,
                                    STATE_MAJORGROUP_YEAR_ID INT,
                                    STATE_ACTIVITY_YEAR_ID   INT,
                                    STATE_SOCIALNEED_YEAR_ID INT,
							        FIPS_ID                  INT,
							        FIPS_YEAR_ID             INT
;
GO

UPDATE		A SET   A.EIN_ID                   = CONVERT(INT, B01.EIN_ID 				  ),
                    A.CATEGORY_ID              = CONVERT(INT, B02.CATEGORY_ID 		      ),
                    A.MAJORGROUP_ID            = CONVERT(INT, B03.MAJORGROUP_ID 		  ),
                    A.ACTIVITY_ID              = CONVERT(INT, B04.ACTIVITY_ID 		      ),
                    A.SOCIALNEED_ID            = CONVERT(INT, B05.SOCIALNEED_ID 		  ),
                    A.STATE_ID                 = CONVERT(INT, B06.STATE_ID 			      ),
                    A.STATE_YEAR_ID            = CONVERT(INT, B07.STATE_YEAR_ID 		  ),
                    A.CATEGORY_YEAR_ID         = CONVERT(INT, B08.CATEGORY_YEAR_ID 	      ),
                    A.MAJORGROUP_YEAR_ID       = CONVERT(INT, B09.MAJORGROUP_YEAR_ID 	  ),
                    A.ACTIVITY_YEAR_ID         = CONVERT(INT, B10.ACTIVITY_YEAR_ID        ),
                    A.SOCIALNEED_YEAR_ID       = CONVERT(INT, B11.SOCIALNEED_YEAR_ID 	  ),
                    A.STATE_CATEGORY_ID        = CONVERT(INT, B12.STATE_CATEGORY_ID       ),
                    A.STATE_MAJORGROUP_ID      = CONVERT(INT, B13.STATE_MAJORGROUP_ID     ),
                    A.STATE_ACTIVITY_ID        = CONVERT(INT, B14.STATE_ACTIVITY_ID       ),
                    A.STATE_SOCIALNEED_ID      = CONVERT(INT, B15.STATE_SOCIALNEED_ID     ),
                    A.STATE_CATEGORY_YEAR_ID   = CONVERT(INT, B16.STATE_CATEGORY_YEAR_ID  ),
                    A.STATE_MAJORGROUP_YEAR_ID = CONVERT(INT, B17.STATE_MAJORGROUP_YEAR_ID),
                    A.STATE_ACTIVITY_YEAR_ID   = CONVERT(INT, B18.STATE_ACTIVITY_YEAR_ID  ),
                    A.STATE_SOCIALNEED_YEAR_ID = CONVERT(INT, B19.STATE_SOCIALNEED_YEAR_ID),
				    A.FIPS_ID                  = CONVERT(INT, B20.FIPS_ID 	              ),
                    A.FIPS_YEAR_ID             = CONVERT(INT, B21.FIPS_YEAR_ID            )
FROM				SAMPLE_CHARITY_YEAR AS A
					LEFT OUTER JOIN
						TEMP_EIN_ID AS B01
					ON	A.EIN = B01.EIN
					
					LEFT OUTER JOIN
						TEMP_CATEGORY_ID AS B02
					ON	A.CATEGORY = B02.CATEGORY
					
					LEFT OUTER JOIN
						TEMP_MAJORGROUP_ID AS B03
					ON	A.MAJORGROUP = B03.MAJORGROUP
					
					LEFT OUTER JOIN
						TEMP_ACTIVITY_ID AS B04
					ON	A.ACTIVITY = B04.ACTIVITY
					
					LEFT OUTER JOIN
						TEMP_SOCIALNEED_ID AS B05
					ON	A.SOCIALNEED = B05.SOCIALNEED

					LEFT OUTER JOIN
						TEMP_STATE_ID AS B06
					ON	A.STATE = B06.STATE	

					LEFT OUTER JOIN
						TEMP_STATE_YEAR_ID AS B07
					ON	A.STATE       = B07.STATE
					AND	A.FISCAL_YEAR = B07.FISCAL_YEAR

					LEFT OUTER JOIN
						TEMP_CATEGORY_YEAR_ID AS B08
					ON	A.CATEGORY    = B08.CATEGORY	
					AND	A.FISCAL_YEAR = B08.FISCAL_YEAR

					LEFT OUTER JOIN
						TEMP_MAJORGROUP_YEAR_ID AS B09
					ON	A.MAJORGROUP  = B09.MAJORGROUP	
					AND	A.FISCAL_YEAR = B09.FISCAL_YEAR

					LEFT OUTER JOIN
						TEMP_ACTIVITY_YEAR_ID AS B10
					ON	A.ACTIVITY    = B10.ACTIVITY	
					AND	A.FISCAL_YEAR = B10.FISCAL_YEAR

					LEFT OUTER JOIN
						TEMP_SOCIALNEED_YEAR_ID AS B11
					ON	A.SOCIALNEED  = B11.SOCIALNEED	
					AND	A.FISCAL_YEAR = B11.FISCAL_YEAR

					LEFT OUTER JOIN
						TEMP_STATE_CATEGORY_ID AS B12
					ON	A.CATEGORY = B12.CATEGORY
					AND	A.STATE    = B12.STATE

					LEFT OUTER JOIN
						TEMP_STATE_MAJORGROUP_ID AS B13
					ON	A.MAJORGROUP = B13.MAJORGROUP
					AND	A.STATE      = B13.STATE

					LEFT OUTER JOIN
						TEMP_STATE_ACTIVITY_ID AS B14
					ON	A.ACTIVITY = B14.ACTIVITY
					AND	A.STATE    = B14.STATE

					LEFT OUTER JOIN
						TEMP_STATE_SOCIALNEED_ID AS B15
					ON	A.SOCIALNEED = B15.SOCIALNEED
					AND	A.STATE      = B15.STATE

					LEFT OUTER JOIN
						TEMP_STATE_CATEGORY_YEAR_ID AS B16
					ON	A.CATEGORY    = B16.CATEGORY
					AND	A.STATE       = B16.STATE
					AND A.FISCAL_YEAR = B16.FISCAL_YEAR

					LEFT OUTER JOIN
						TEMP_STATE_MAJORGROUP_YEAR_ID AS B17
					ON	A.MAJORGROUP  = B17.MAJORGROUP
					AND	A.STATE       = B17.STATE
					AND A.FISCAL_YEAR = B17.FISCAL_YEAR

					LEFT OUTER JOIN
						TEMP_STATE_ACTIVITY_YEAR_ID AS B18
					ON	A.ACTIVITY    = B18.ACTIVITY
					AND	A.STATE       = B18.STATE
					AND A.FISCAL_YEAR = B18.FISCAL_YEAR

					LEFT OUTER JOIN
						TEMP_STATE_SOCIALNEED_YEAR_ID AS B19
					ON	A.SOCIALNEED  = B19.SOCIALNEED
					AND	A.STATE       = B19.STATE
					AND A.FISCAL_YEAR = B19.FISCAL_YEAR

					LEFT OUTER JOIN
						TEMP_FIPS_ID AS B20
					ON	A.FIPS = B20.FIPS

					LEFT OUTER JOIN
						TEMP_FIPS_YEAR_ID AS B21
					ON	A.FIPS        = B21.FIPS
					AND	A.FISCAL_YEAR = B21.FISCAL_YEAR
;

/* Drop intermediary tables */
DROP TABLE TEMP_EIN_ID                  ;
DROP TABLE TEMP_MAJORGROUP_ID           ;
DROP TABLE TEMP_SOCIALNEED_ID           ;
DROP TABLE TEMP_ACTIVITY_ID             ;
DROP TABLE TEMP_CATEGORY_ID             ;
DROP TABLE TEMP_STATE_ID                ;
DROP TABLE TEMP_STATE_YEAR_ID           ;
DROP TABLE TEMP_MAJORGROUP_YEAR_ID      ;
DROP TABLE TEMP_SOCIALNEED_YEAR_ID      ;
DROP TABLE TEMP_ACTIVITY_YEAR_ID        ;
DROP TABLE TEMP_CATEGORY_YEAR_ID        ;
DROP TABLE TEMP_STATE_MAJORGROUP_ID     ;
DROP TABLE TEMP_STATE_SOCIALNEED_ID     ;
DROP TABLE TEMP_STATE_ACTIVITY_ID       ;
DROP TABLE TEMP_STATE_CATEGORY_ID       ;
DROP TABLE TEMP_STATE_MAJORGROUP_YEAR_ID;
DROP TABLE TEMP_STATE_SOCIALNEED_YEAR_ID;
DROP TABLE TEMP_STATE_ACTIVITY_YEAR_ID  ;
DROP TABLE TEMP_STATE_CATEGORY_YEAR_ID  ;
DROP TABLE TEMP_FIPS_ID                 ;
DROP TABLE TEMP_FIPS_YEAR_ID            ;


/* **************************************************************************************** */
/* Section 3: Variables and tables for analyses                                             */
/* **************************************************************************************** */

/* **************************************************************************************** */
/* Sample A */

/* Code instrument for Sample A */
-- For each observation, produce additional combinations of each "simulated state" 
SELECT 						A.FISCAL_YEAR            ,
							A.EIN                    ,
							A.ACTIVITY               ,
							A.PERIOD_END             ,
							A.REVENUE_TOTAL          ,
							A.RD_REV_LESS_GRANTS     ,
							A.RD_CONTRIB_LESS_GRANTS ,
							A.RD_AVG_REV_3YR         ,
							SIMULATED_STATE = B.STATE  
INTO						SMPL_A_INSTRUMENT1							
FROM						SAMPLE_CHARITY_YEAR AS A,
							(			
							SELECT DISTINCT	STATE
							FROM			SAMPLE_CHARITY_YEAR
							) AS B									
;							

-- For each simulated observation, determine if the chariy would be required to obtain an audit 
SELECT						A.*,                         
							MANDATORY_AUDIT_SIM = 
							CASE
								WHEN A.REVENUE_TOTAL          > B.REVENUE_TOTAL_CUTOFF       THEN 1
								WHEN A.RD_REV_LESS_GRANTS     > B.REV_LESS_GRANTS_CUTOFF     THEN 1
								WHEN A.RD_AVG_REV_3YR         > B.AVG_REV_3YR_CUTOFF         THEN 1
								WHEN A.RD_CONTRIB_LESS_GRANTS > B.CONTRIB_LESS_GRANTS_CUTOFF THEN 1
								ELSE 0
							END							
INTO						SMPL_A_INSTRUMENT2							
FROM						SMPL_A_INSTRUMENT1 AS A 
						LEFT OUTER JOIN
							JMP_AuditRegulations_Handcollected.DBO.STATE_AUDITREG_HANDCOLLECTED AS B
						ON	A.SIMULATED_STATE = B.STATE	
						AND	A.PERIOD_END      > B.DATE_EFFECTIVE
						AND A.PERIOD_END      < B.DATE_INEFFECTIVE
;					

-- Aggregate at by activity-state-year  
SELECT						A.*                                                                                     ,
							MANDATORY_AUDIT_SHARE      = ISNULL(D.MANDATORY_AUDIT_NUMBER    ,0) / C.TOTAL_NUMBER    ,
							INST_MANDATORY_AUDIT_SHARE = ISNULL(F.MANDATORY_AUDIT_SIM_NUMBER,0) / E.TOTAL_SIM_NUMBER                                
INTO						SMPL_A_INSTRUMENT							
FROM			
							(
							SELECT DISTINCT	FISCAL_YEAR           ,
											STATE                 ,
											ACTIVITY              ,
											STATE_ACTIVITY_YEAR_ID,
											STATE_YEAR_ID         ,
											ACTIVITY_YEAR_ID      ,
											STATE_ACTIVITY_ID     ,
											STATE_ID              ,
											ACTIVITY_ID
							FROM			SAMPLE_CHARITY_YEAR 
							) AS A		
							
						LEFT OUTER JOIN
							(
							SELECT			STATE                                 ,
											ACTIVITY                              ,
											FISCAL_YEAR                           ,
											TOTAL_NUMBER = CONVERT(FLOAT,COUNT(*)) 
							FROM			SAMPLE_CHARITY_YEAR 
							GROUP BY		STATE      ,
											ACTIVITY   ,
											FISCAL_YEAR
							) AS C
						ON	A.STATE       = C.STATE
						AND	A.ACTIVITY    = C.ACTIVITY
						AND	A.FISCAL_YEAR = C.FISCAL_YEAR
						
						LEFT OUTER JOIN
							(
							SELECT			STATE                                           ,
											ACTIVITY                                        ,
											FISCAL_YEAR                                     ,
											MANDATORY_AUDIT_NUMBER = CONVERT(FLOAT,COUNT(*))
							FROM			SAMPLE_CHARITY_YEAR 
							WHERE			RD_AUDIT_REQ = 1
							GROUP BY		STATE      ,
											ACTIVITY   ,
											FISCAL_YEAR
							) AS D
						ON	A.STATE       = D.STATE
						AND	A.ACTIVITY    = D.ACTIVITY
						AND	A.FISCAL_YEAR = D.FISCAL_YEAR	
						
						LEFT OUTER JOIN	
							(
							SELECT			SIMULATED_STATE                           ,
											ACTIVITY                                  ,
											FISCAL_YEAR                               ,
											TOTAL_SIM_NUMBER = CONVERT(FLOAT,COUNT(*)) 
							FROM			SMPL_A_INSTRUMENT2
							GROUP BY		SIMULATED_STATE,
											ACTIVITY       ,
											FISCAL_YEAR
							) AS E
						ON	A.STATE       = E.SIMULATED_STATE
						AND	A.ACTIVITY    = E.ACTIVITY
						AND	A.FISCAL_YEAR = E.FISCAL_YEAR	
						
						LEFT OUTER JOIN 
							(
							SELECT			SIMULATED_STATE                                     ,
											ACTIVITY                                            ,
											FISCAL_YEAR                                         ,
											MANDATORY_AUDIT_SIM_NUMBER = CONVERT(FLOAT,COUNT(*))  
							FROM			SMPL_A_INSTRUMENT2
							WHERE			MANDATORY_AUDIT_SIM = 1
							GROUP BY		SIMULATED_STATE,
											ACTIVITY       ,
											FISCAL_YEAR
							) AS F
						ON	A.STATE       = F.SIMULATED_STATE
						AND	A.ACTIVITY    = F.ACTIVITY
						AND	A.FISCAL_YEAR = F.FISCAL_YEAR
;			
								
-- Drop intermediary tables 
DROP TABLE SMPL_A_INSTRUMENT1;
DROP TABLE SMPL_A_INSTRUMENT2;

/* Code variables for instrument's Role */
SELECT			A.STATE				 ,
				A.FISCAL_YEAR        ,
				A.ACTIVITY           ,
				A.NUMBER_CHARITIES   ,
				A.LN_NUMBER_CHARITIES,
				A.AVG_DONATIONS      ,
				A.LN_AVG_DONATIONS   ,
				A.AVG_ASSETS         ,
				A.LN_AVG_ASSETS      ,
				B.RELIANCE_DONATION  ,    
				C.OPERATING_COST
INTO			SMPL_A_INSTRUMENT_ROLE				
FROM			
				(
				SELECT			STATE			                                 	,
								FISCAL_YEAR                                   	    ,
								ACTIVITY                                   	        ,
								NUMBER_CHARITIES    = CONVERT(FLOAT,COUNT(*))       ,
								LN_NUMBER_CHARITIES = LOG(1+CONVERT(FLOAT,COUNT(*))),
								AVG_DONATIONS       = AVG(CONTRIB_ALL_W)            ,
								LN_AVG_DONATIONS	= LOG(1+AVG(CONTRIB_ALL_W))     , 
								AVG_ASSETS		    = AVG(ASSETS_TOTAL_EOY)         ,
								LN_AVG_ASSETS	    = LOG(1+AVG(ASSETS_TOTAL_EOY))     
				FROM			SAMPLE_CHARITY_YEAR
				GROUP BY		STATE      ,
								FISCAL_YEAR,
								ACTIVITY
				) AS A				
			LEFT OUTER JOIN
				(
				SELECT			FISCAL_YEAR                                                                                                                                              ,
								ACTIVITY                                                                                                                                                 ,
								RELIANCE_DONATION = AVG(CASE WHEN CONTRIB_ALL_W >= REVENUE_TOTAL AND CONTRIB_ALL_W > 0 THEN 1 ELSE ISNULL(CONTRIB_ALL_W,0) / NULLIF(REVENUE_TOTAL,0) END)
				FROM			SAMPLE_CHARITY_YEAR
				GROUP BY		FISCAL_YEAR,
								ACTIVITY
				) AS B
			ON	A.FISCAL_YEAR = B.FISCAL_YEAR
			AND	A.ACTIVITY    = B.ACTIVITY
			LEFT OUTER JOIN
				(
				SELECT			FISCAL_YEAR                               				                                                   ,
								ACTIVITY                               				                                                       ,
							   	OPERATING_COST = AVG(PPE_NET / CASE WHEN ASSETS_TOTAL_EOY < PPE_NET THEN PPE_NET ELSE ASSETS_TOTAL_EOY END)
				FROM			SAMPLE_CHARITY_YEAR
				WHERE			PPE_NET > 0
				GROUP BY		FISCAL_YEAR,
								ACTIVITY
				) AS C
			ON	A.FISCAL_YEAR = C.FISCAL_YEAR
			AND	A.ACTIVITY    = C.ACTIVITY
;		

/* Code ConcentrationDonations */
SELECT 			A.STATE                                                                       ,
				A.ACTIVITY                                                                    ,
				A.FISCAL_YEAR                                                                 ,
				CONCENTRATION_DONATIONS = SUM(POWER((A.CONTRIB_ALL_W / B.DONATIONS_TOTAL), 2))
INTO			SMPL_A_CONCENTRATION_DONATIONS				
FROM			
				SAMPLE_CHARITY_YEAR AS A
			LEFT OUTER JOIN
				(
				SELECT 			AA.STATE							   ,				
								AA.ACTIVITY                            ,
								AA.FISCAL_YEAR                         ,
								DONATIONS_TOTAL = SUM(AA.CONTRIB_ALL_W) 
				FROM			SAMPLE_CHARITY_YEAR AS AA
							LEFT OUTER JOIN
								(
								SELECT DISTINCT	EIN
								FROM			SAMPLE_CHARITY_YEAR
								WHERE			GROUP_FILING = 1
								) AS BB
							ON	AA.EIN = BB.EIN
				WHERE			BB.EIN IS NULL 			
				GROUP BY		AA.STATE      ,							
								AA.ACTIVITY   ,
								AA.FISCAL_YEAR	
				HAVING			SUM(AA.CONTRIB_ALL_W) > 0
				) AS B
				ON	A.STATE       = B.STATE
				AND	A.ACTIVITY    = B.ACTIVITY
				AND	A.FISCAL_YEAR = B.FISCAL_YEAR
			LEFT OUTER JOIN
				(
				SELECT DISTINCT	EIN
				FROM			SAMPLE_CHARITY_YEAR
				WHERE			GROUP_FILING = 1
				) AS BBB
			ON	A.EIN = BBB.EIN
WHERE			BBB.EIN IS NULL 							
GROUP BY		A.STATE      ,					
				A.ACTIVITY   ,
				A.FISCAL_YEAR				
;		

/* Code LnTotalDonations */
SELECT			A.STATE				                                                                          ,		
				A.ACTIVITY                                                                                    ,
				A.FISCAL_YEAR                                                                                 ,
				A.TOTAL_DONATIONS                                                                             ,
				LN_TOTAL_DONATIONS = LOG(1+ CASE WHEN A.TOTAL_DONATIONS < 0 THEN 0 ELSE A.TOTAL_DONATIONS END)
INTO			SMPL_A_TOTAL_DONATIONS				
FROM			
				(
				SELECT 			STATE						        ,			
								ACTIVITY                            ,
								FISCAL_YEAR                         ,
								TOTAL_DONATIONS = SUM(CONTRIB_ALL_W)	
				FROM			SAMPLE_CHARITY_YEAR 			
				GROUP BY		STATE      ,					
								ACTIVITY   ,
								FISCAL_YEAR
				) AS A			
;

/* Code GeographicConcentration */ 
-- Compute total donations by city-activity-state-year 
SELECT			A.STATE                                    ,
				A.FISCAL_YEAR                              ,
				A.ACTIVITY                                 ,
				A.CITY                                     ,
				DONATIONS_TOTAL_CITY = SUM(A.CONTRIB_ALL_W)
INTO			SMPL_A_GEO_CONCENTRATION1
FROM			SAMPLE_CHARITY_YEAR AS A
			LEFT OUTER JOIN
				(
				SELECT DISTINCT	EIN
				FROM			SAMPLE_CHARITY_YEAR
				WHERE			GROUP_FILING = 1
				) AS B	
			ON	A.EIN = B.EIN 
WHERE			B.EIN IS NULL 
GROUP BY		A.STATE      , 
				A.FISCAL_YEAR, 
				A.ACTIVITY   ,
				A.CITY
;

-- Compute HHI to capture how concentrated donations are among cities for each activity-state-year 
SELECT 			A.STATE			                                          	                                    ,
				A.ACTIVITY                                          	                                        ,
				A.FISCAL_YEAR                                          	                                        ,
				GEOGRAPHIC_CONCENTRATION = SUM(POWER((A.DONATIONS_TOTAL_CITY / NULLIF(B.DONATIONS_TOTAL,0)), 2))
INTO			SMPL_A_GEO_CONCENTRATION			
FROM			SMPL_A_GEO_CONCENTRATION1 AS A
			LEFT OUTER JOIN
				(
				SELECT 			STATE				                       ,
								ACTIVITY                                   ,
								FISCAL_YEAR                                ,
								DONATIONS_TOTAL = SUM(DONATIONS_TOTAL_CITY)
				FROM			SMPL_A_GEO_CONCENTRATION1		
				GROUP BY		STATE      ,					
								ACTIVITY   ,
								FISCAL_YEAR	
				) AS B
				ON	A.STATE       = B.STATE
				AND	A.ACTIVITY    = B.ACTIVITY
				AND	A.FISCAL_YEAR = B.FISCAL_YEAR							
GROUP BY		A.STATE      ,					
				A.ACTIVITY   ,
				A.FISCAL_YEAR				
;		

-- Drop intermediary tables 
DROP TABLE SMPL_A_GEO_CONCENTRATION1;

/* Code Concentration of donations in largest city  */
-- Compute total donations by city-activity-state-year 
SELECT			A.STATE                               ,
				A.FISCAL_YEAR                         ,
				A.ACTIVITY                            ,
				A.CITY                                ,
				DONATIONS_TOTAL = SUM(A.CONTRIB_ALL_W)
INTO			SMPL_A_LARGEST_CITY1
FROM			SAMPLE_CHARITY_YEAR AS A
			LEFT OUTER JOIN 
				(
				SELECT DISTINCT	EIN
				FROM			SAMPLE_CHARITY_YEAR
				WHERE			GROUP_FILING = 1
				) AS B	
			ON	A.EIN = B.EIN 
WHERE			B.EIN IS NULL			
GROUP BY		A.STATE      , 
				A.FISCAL_YEAR, 
				A.ACTIVITY   ,
				A.CITY
;

-- Compute the share of donations held by the largest city for each activity-state-year 
SELECT 			STATE			                                          	              ,
				ACTIVITY                                          	                      ,
				FISCAL_YEAR                                          	                  ,
				SHARE_LARGEST_CITY = MAX(DONATIONS_TOTAL) / NULLIF(SUM(DONATIONS_TOTAL),0)
INTO			SMPL_A_LARGEST_CITY				
FROM			SMPL_A_LARGEST_CITY1 						
GROUP BY		STATE      ,					
				ACTIVITY   ,
				FISCAL_YEAR				
;		

-- Drop intermediary tables  
DROP TABLE SMPL_A_LARGEST_CITY1;

/* Code SocialNeedConcentration */ 

-- Compute total donations by SocialNeed-state-year 
SELECT			A.STATE                                           ,
				A.FISCAL_YEAR                                     ,
				A.SOCIALNEED                                      ,
				A.ACTIVITY                                        ,
				DONATIONS_TOTAL_SOCIAL_NEED = SUM(A.CONTRIB_ALL_W)
INTO			SMPL_A_SOCIAL_NEED1
FROM			SAMPLE_CHARITY_YEAR AS A
			LEFT OUTER JOIN
				(
				SELECT DISTINCT	EIN
				FROM			SAMPLE_CHARITY_YEAR
				WHERE			GROUP_FILING = 1
				) AS B	
			ON	A.EIN = B.EIN 
WHERE			B.EIN IS NULL 
GROUP BY		A.STATE      , 
				A.FISCAL_YEAR, 
				A.ACTIVITY   , 
				A.SOCIALNEED 
;

-- Compute HHI to capture how concentrated donations are among social needs for each activity-state-year 
SELECT 			A.STATE			                                          	                                       		,
				A.ACTIVITY                                          	                                       		    ,
				A.FISCAL_YEAR                                          	                                       		    ,
				SOCIAL_NEED_CONCENTRATION = SUM(POWER((A.DONATIONS_TOTAL_SOCIAL_NEED / NULLIF(B.DONATIONS_TOTAL,0)), 2))
INTO			SMPL_A_SOCIAL_NEED				
FROM			SMPL_A_SOCIAL_NEED1 AS A
			LEFT OUTER JOIN
				(
				SELECT 			STATE				                              ,
								ACTIVITY                                          ,
								FISCAL_YEAR                                       ,
								DONATIONS_TOTAL = SUM(DONATIONS_TOTAL_SOCIAL_NEED)
				FROM			SMPL_A_SOCIAL_NEED1		
				GROUP BY		STATE      ,					
								ACTIVITY   ,
								FISCAL_YEAR	
				HAVING			SUM(DONATIONS_TOTAL_SOCIAL_NEED) > 0
				) AS B
				ON	A.STATE       = B.STATE
				AND	A.ACTIVITY    = B.ACTIVITY
				AND	A.FISCAL_YEAR = B.FISCAL_YEAR							
GROUP BY		A.STATE      ,					
				A.ACTIVITY   ,
				A.FISCAL_YEAR				
;		

-- Drop intermediary table 
DROP TABLE SMPL_A_SOCIAL_NEED1;

/* Merge variables for Sample A */
SELECT				A.FISCAL_YEAR               ,
					A.ACTIVITY                  ,
					A.STATE                     ,
					A.STATE_ID                  ,
					A.STATE_YEAR_ID             ,
					A.ACTIVITY_YEAR_ID          ,
					A.STATE_ACTIVITY_ID         ,
					A.MANDATORY_AUDIT_SHARE     ,
					A.INST_MANDATORY_AUDIT_SHARE,
					B.EXEMPT                    ,
					B.LOW_IA                    ,
					B.HIGH_IA                   ,
					C.CONCENTRATION_DONATIONS   ,
					D.TOTAL_DONATIONS           ,
					D.LN_TOTAL_DONATIONS        ,
					E.GEOGRAPHIC_CONCENTRATION  ,
					F.SOCIAL_NEED_CONCENTRATION ,
					G.SHARE_LARGEST_CITY        ,
					H.NUMBER_CHARITIES          ,
					H.LN_NUMBER_CHARITIES       ,
					H.AVG_DONATIONS             ,
					H.LN_AVG_DONATIONS          ,
					H.AVG_ASSETS                ,
					H.LN_AVG_ASSETS             ,
					H.RELIANCE_DONATION         ,
					H.OPERATING_COST            ,
					I.LN_POPULATION             ,
					J.GDP_PER_CAPITA            ,
					K.UNEMPLOYMENT_RATE                   
INTO                SMPL_A_VARIABLES
FROM				SMPL_A_INSTRUMENT AS A
				
				LEFT OUTER JOIN
					(
					SELECT DISTINCT STATE      ,
									ACTIVITY   ,
									FISCAL_YEAR,
									EXEMPT     ,
									LOW_IA     ,
									HIGH_IA
					FROM			SAMPLE_CHARITY_YEAR
					) AS B
				ON	A.STATE       = B.STATE					
				AND	A.ACTIVITY    = B.ACTIVITY
				AND	A.FISCAL_YEAR = B.FISCAL_YEAR
				
				LEFT OUTER JOIN
					SMPL_A_CONCENTRATION_DONATIONS AS C
				ON	A.STATE       = C.STATE					
				AND	A.ACTIVITY    = C.ACTIVITY
				AND	A.FISCAL_YEAR = C.FISCAL_YEAR	
				
				LEFT OUTER JOIN
					SMPL_A_TOTAL_DONATIONS AS D
				ON	A.STATE       = D.STATE					
				AND	A.ACTIVITY    = D.ACTIVITY
				AND	A.FISCAL_YEAR = D.FISCAL_YEAR		

				LEFT OUTER JOIN
					SMPL_A_GEO_CONCENTRATION AS E
				ON	A.STATE       = E.STATE					
				AND	A.ACTIVITY    = E.ACTIVITY
				AND	A.FISCAL_YEAR = E.FISCAL_YEAR	
				
				LEFT OUTER JOIN
					SMPL_A_SOCIAL_NEED AS F
				ON	A.STATE       = F.STATE					
				AND	A.ACTIVITY    = F.ACTIVITY
				AND	A.FISCAL_YEAR = F.FISCAL_YEAR		
				
				LEFT OUTER JOIN	
					SMPL_A_LARGEST_CITY AS G
				ON	A.STATE       = G.STATE          
				AND	A.ACTIVITY    = G.ACTIVITY 
				AND	A.FISCAL_YEAR = G.FISCAL_YEAR 
				
				LEFT OUTER JOIN	
					SMPL_A_INSTRUMENT_ROLE AS H
				ON	A.STATE       = H.STATE          
				AND	A.ACTIVITY    = H.ACTIVITY 
				AND	A.FISCAL_YEAR = H.FISCAL_YEAR
				
				LEFT OUTER JOIN
					CENSUS_POP_CLEANSED AS I
				ON	A.STATE       = I.STATE
				AND	A.FISCAL_YEAR = I.YEAR
				
				LEFT OUTER JOIN
					BEA_GDP_CLEANSED AS J
				ON	A.STATE       = J.STATE					
				AND	A.FISCAL_YEAR = J.YEAR
				
				LEFT OUTER JOIN
					BLS_UNEMPLOYMENT_CLEANSED AS K
				ON	A.STATE       = K.STATE					
				AND	A.FISCAL_YEAR = K.YEAR	
;

/* Create Sample A */
WITH SMPL_A_1 AS
(
SELECT			*
FROM			SMPL_A_VARIABLES
WHERE			FISCAL_YEAR >= 1998
			AND	FISCAL_YEAR <= 2015
			AND	EXEMPT = 0
)
SELECT			A.*                                                        ,
				L_INST_MANDATORY_AUDIT_SHARE = B.INST_MANDATORY_AUDIT_SHARE,
				L_MANDATORY_AUDIT_SHARE      = B.MANDATORY_AUDIT_SHARE     
INTO			SMPL_A
FROM			SMPL_A_1 AS A
			LEFT OUTER JOIN
				SMPL_A_1 AS B
			ON	A.STATE    = B.STATE
			AND	A.ACTIVITY = B.ACTIVITY
			AND	(A.FISCAL_YEAR - B.FISCAL_YEAR) = 1
WHERE			B.MANDATORY_AUDIT_SHARE    IS NOT NULL
			AND	A.LN_TOTAL_DONATIONS       IS NOT NULL
			AND	A.CONCENTRATION_DONATIONS  IS NOT NULL
			AND	A.GEOGRAPHIC_CONCENTRATION IS NOT NULL
			AND A.OPERATING_COST           IS NOT NULL
;

/* **************************************************************************************** */
/* Sample B */

/* Code RatingsCoverage */
SELECT DISTINCT FISCAL_YEAR                                                                                                                                                                           ,
				ACTIVITY                                                                                                                                                                              ,
				STATE                                                                                                                                                                                 ,
				RATING_COVERAGE = ISNULL(CONVERT(FLOAT, SUM(CN_RATED) OVER (PARTITION BY FISCAL_YEAR, CATEGORY, STATE)) / CONVERT(FLOAT, COUNT(*) OVER (PARTITION BY FISCAL_YEAR, CATEGORY, STATE)),0)
INTO			SMPL_B_RATING_COVERAGE
FROM			SAMPLE_CHARITY_YEAR
WHERE			FISCAL_YEAR >= 2003
;

/* Merge variables for sample B */
SELECT			A.*               ,
				B.RATING_COVERAGE ,
				C.AVG_RATING_SCORE
INTO            SMPL_B_VARIABLES
FROM			SMPL_A_VARIABLES AS A
			LEFT OUTER JOIN
				SMPL_B_RATING_COVERAGE AS B
			ON	A.STATE       = B.STATE
			AND	A.FISCAL_YEAR = B.FISCAL_YEAR	
			AND	A.ACTIVITY    = B.ACTIVITY		
			LEFT OUTER JOIN
				(
				SELECT			STATE                                  ,
								FISCAL_YEAR                            ,
								ACTIVITY                               ,
								AVG_RATING_SCORE = AVG(CN_RATING_SCORE)
				FROM			SAMPLE_CHARITY_YEAR
				GROUP BY		STATE      ,
								FISCAL_YEAR,
								ACTIVITY
				) AS C
			ON	A.STATE       = C.STATE
			AND	A.FISCAL_YEAR = C.FISCAL_YEAR	
			AND	A.ACTIVITY    = C.ACTIVITY	
;

/* Create Sample B */
WITH SMPL_B_1 AS 
(
SELECT			*
FROM			SMPL_B_VARIABLES
WHERE			FISCAL_YEAR >= 1998
			AND	FISCAL_YEAR <= 2015
			AND	EXEMPT       = 0
)
SELECT			A.*                                                        ,                                                                                                                                                                                                                                                            
				L_INST_MANDATORY_AUDIT_SHARE = B.INST_MANDATORY_AUDIT_SHARE                                                                                                                                                                                                                                         
INTO			SMPL_B_2
FROM			SMPL_B_1 AS A
			LEFT OUTER JOIN
				SMPL_B_1 AS B
			ON	A.STATE    = B.STATE
			AND	A.ACTIVITY = B.ACTIVITY
			AND	(A.FISCAL_YEAR - B.FISCAL_YEAR) = 1
WHERE			B.INST_MANDATORY_AUDIT_SHARE IS NOT NULL
			AND	A.LN_TOTAL_DONATIONS         IS NOT NULL
			AND	A.CONCENTRATION_DONATIONS    IS NOT NULL
			AND	A.GEOGRAPHIC_CONCENTRATION   IS NOT NULL
			AND A.OPERATING_COST             IS NOT NULL
;

-- Standardize ratings coverage and drop pre2003 (i.e., CN data goes as far as 2003)
SELECT			*,
				RATINGS_COVERAGE_STD = (RATING_COVERAGE - AVG(RATING_COVERAGE) OVER ()) / STDEV  (RATING_COVERAGE) OVER ()
INTO			SMPL_B
FROM			SMPL_B_2
WHERE			FISCAL_YEAR >= 2003
;

-- Drop intermediary table
DROP TABLE SMPL_B_2;

/* **************************************************************************************** */
/* Sample C */

/* Compute instrument for Sample C */
-- For each observation, produce additional combinations of each "simulated state" 
SELECT 						A.FISCAL_YEAR            ,
							A.EIN                    ,
							A.PERIOD_END             ,
							A.REVENUE_TOTAL          ,
							A.RD_REV_LESS_GRANTS     ,
							A.RD_CONTRIB_LESS_GRANTS ,
							A.RD_AVG_REV_3YR         ,
							SIMULATED_STATE = B.STATE
INTO						SMPL_C_INSTRUMENT01							
FROM						SAMPLE_CHARITY_YEAR AS A,
							(			
							SELECT DISTINCT	STATE
							FROM			SAMPLE_CHARITY_YEAR
							) AS B									
;							

-- For each simulated observation, determine if the chariy would be required to obtain an audit 
SELECT						A.*,                         
							MANDATORY_AUDIT_SIM = 
							CASE
								WHEN A.REVENUE_TOTAL          > B.REVENUE_TOTAL_CUTOFF 		 THEN 1
								WHEN A.RD_REV_LESS_GRANTS     > B.REV_LESS_GRANTS_CUTOFF 	 THEN 1
								WHEN A.RD_AVG_REV_3YR         > B.AVG_REV_3YR_CUTOFF 		 THEN 1
								WHEN A.RD_CONTRIB_LESS_GRANTS > B.CONTRIB_LESS_GRANTS_CUTOFF THEN 1
								ELSE 0
							END							
INTO						SMPL_C_INSTRUMENT02							
FROM						SMPL_C_INSTRUMENT01 AS A 
						LEFT OUTER JOIN
							JMP_AuditRegulations_Handcollected.DBO.STATE_AUDITREG_HANDCOLLECTED AS B
						ON	A.SIMULATED_STATE = B.STATE	
						AND	A.PERIOD_END      > B.DATE_EFFECTIVE
						AND A.PERIOD_END      < B.DATE_INEFFECTIVE
;					

-- Aggregate at state-year level 
SELECT						A.*                                                                                     ,
							MANDATORY_AUDIT_SHARE      = ISNULL(D.MANDATORY_AUDIT_NUMBER    ,0) / C.TOTAL_NUMBER    ,
							INST_MANDATORY_AUDIT_SHARE = ISNULL(F.MANDATORY_AUDIT_SIM_NUMBER,0) / E.TOTAL_SIM_NUMBER                                
INTO						SMPL_C_INSTRUMENT							
FROM			
							(
							SELECT DISTINCT	FISCAL_YEAR  ,
											STATE        ,
											STATE_YEAR_ID,
											STATE_ID
							FROM			SAMPLE_CHARITY_YEAR 
							) AS A		
							
						LEFT OUTER JOIN
							(
							SELECT			STATE                                 ,
											FISCAL_YEAR                           ,
											TOTAL_NUMBER = CONVERT(FLOAT,COUNT(*)) 
							FROM			SAMPLE_CHARITY_YEAR
							GROUP BY		STATE      ,
											FISCAL_YEAR
							) AS C
						ON	A.STATE       = C.STATE
						AND	A.FISCAL_YEAR = C.FISCAL_YEAR
						
						LEFT OUTER JOIN
							(
							SELECT			STATE                                           ,
											FISCAL_YEAR                                     ,
											MANDATORY_AUDIT_NUMBER = CONVERT(FLOAT,COUNT(*))
							FROM			SAMPLE_CHARITY_YEAR
							WHERE			RD_AUDIT_REQ = 1
							GROUP BY		STATE      ,
											FISCAL_YEAR
							) AS D
						ON	A.STATE       = D.STATE
						AND	A.FISCAL_YEAR = D.FISCAL_YEAR	
						
						LEFT OUTER JOIN	
							(
							SELECT			SIMULATED_STATE                           ,
											FISCAL_YEAR                               ,
											TOTAL_SIM_NUMBER = CONVERT(FLOAT,COUNT(*)) 
							FROM			SMPL_C_INSTRUMENT02
							GROUP BY		SIMULATED_STATE,
											FISCAL_YEAR
							) AS E
						ON	A.STATE       = E.SIMULATED_STATE
						AND	A.FISCAL_YEAR = E.FISCAL_YEAR	
						
						LEFT OUTER JOIN 
							(
							SELECT			SIMULATED_STATE                                     ,
											FISCAL_YEAR                                         ,
											MANDATORY_AUDIT_SIM_NUMBER = CONVERT(FLOAT,COUNT(*))  
							FROM			SMPL_C_INSTRUMENT02
							WHERE			MANDATORY_AUDIT_SIM = 1
							GROUP BY		SIMULATED_STATE,
											FISCAL_YEAR
							) AS F
						ON	A.STATE       = F.SIMULATED_STATE
						AND	A.FISCAL_YEAR = F.FISCAL_YEAR		
;			

-- Drop intermediary tables 
DROP TABLE SMPL_C_INSTRUMENT01;
DROP TABLE SMPL_C_INSTRUMENT02;

/* Code TaxPayersWhoGive and merge variables */
SELECT				A.*                       ,
					B.TAXPAYERS_WHO_GIVE_SHARE,
					B.ADJ_GROSS_INCOME        ,
					C.GDP_PER_CAPITA          ,
					D.UNEMPLOYMENT_RATE
INTO				SMPL_C_VARIABLES					
FROM				(
					SELECT		*
					FROM		SMPL_C_INSTRUMENT
					WHERE		FISCAL_YEAR >= 1998
							AND	FISCAL_YEAR <= 2015
					) AS A
				LEFT OUTER JOIN
					(
					SELECT		FISCAL_YEAR									                                  ,
								STATE                                                 		                  ,
								TAXPAYERS_WHO_GIVE_SHARE = SUM(CONTRIBUTIONS_NB) / SUM(ITEMIZED_DEDUCTIONS_NB),
								ADJ_GROSS_INCOME         = SUM(AGI             ) / SUM(ITEMIZED_DEDUCTIONS_NB) 
					FROM		SOI_STATE_CLEANSED
					GROUP BY	STATE      ,
								FISCAL_YEAR					
					) AS B
				ON	A.STATE       = B.STATE
				AND	A.FISCAL_YEAR = B.FISCAL_YEAR
				LEFT OUTER JOIN
					BEA_GDP_CLEANSED AS C
				ON	A.STATE       = C.STATE
				AND	A.FISCAL_YEAR = C.YEAR
				LEFT OUTER JOIN
					BLS_UNEMPLOYMENT_CLEANSED AS D
				ON	A.STATE       = D.STATE
				AND	A.FISCAL_YEAR = D.YEAR
;				

/* Create Sample C */
WITH SMPL_C_01 AS
(
SELECT			*
FROM			SMPL_C_VARIABLES
WHERE			FISCAL_YEAR >= 1998
			AND	FISCAL_YEAR <= 2015
)
SELECT			A.*                                                        ,
				L_INST_MANDATORY_AUDIT_SHARE = B.INST_MANDATORY_AUDIT_SHARE,
				L_MANDATORY_AUDIT_SHARE      = B.MANDATORY_AUDIT_SHARE     
INTO			SMPL_C
FROM			SMPL_C_01 AS A
			LEFT OUTER JOIN
				SMPL_C_01 AS B
			ON	A.STATE = B.STATE
			AND	(A.FISCAL_YEAR - B.FISCAL_YEAR) = 1
WHERE			B.INST_MANDATORY_AUDIT_SHARE IS NOT NULL 	
			AND A.ADJ_GROSS_INCOME           IS NOT NULL 
;

/* **************************************************************************************** */
/* Sample D */

/* Code variables for Sample D: 
- Compute average number of people claim deduction for charitable giving
- Compute control for average adjusted gross income
- Retrieve control variables from other tables
*/

SELECT				A.*                       ,
					A2.MANDATORY_AUDIT_SHARE  ,
					B.TAXPAYERS_WHO_GIVE_SHARE,
					B.ADJ_GROSS_INCOME        ,
					C.UNEMPLOYMENT_RATE       ,
					D.GDP_PER_CAPITA      
INTO				SMPL_D_VARIABLES
FROM				
					(
					SELECT DISTINCT	FIPS	   ,
									STATE      ,
									FISCAL_YEAR,
									FIPS_ID    ,
									STATE_ID   ,
									STATE_YEAR_ID
					FROM			SAMPLE_CHARITY_YEAR
					) AS A
				INNER JOIN
					(
					SELECT			FIPS																	          ,
									FISCAL_YEAR	                                                                      ,
									MANDATORY_AUDIT_SHARE = CONVERT(FLOAT,SUM(RD_AUDIT_REQ)) / CONVERT(FLOAT,COUNT(*))			
					FROM			SAMPLE_CHARITY_YEAR
					GROUP BY		FIPS       ,
									FISCAL_YEAR
					) AS A2
				ON	A.FIPS        = A2.FIPS
				AND A.FISCAL_YEAR = A2.FISCAL_YEAR
				LEFT OUTER JOIN
					(
					SELECT		FISCAL_YEAR									                                            ,
								FIPS                                                 		                            ,
								TAXPAYERS_WHO_GIVE_SHARE = SUM(CONTRIBUTIONS_NB) / NULLIF(SUM(ITEMIZED_DEDUCTIONS_NB),0),
								ADJ_GROSS_INCOME         = SUM(AGI)              / NULLIF(SUM(ITEMIZED_DEDUCTIONS_NB),0)	
					FROM		SOI_FIPS_CLEANSED
					WHERE		CONTRIBUTIONS_NB < ITEMIZED_DEDUCTIONS_NB
					GROUP BY	FIPS       ,
								FISCAL_YEAR			
					) AS B
				ON	A.FIPS        = B.FIPS
				AND A.FISCAL_YEAR = B.FISCAL_YEAR
				LEFT OUTER JOIN
					BLS_UNEMPLOYMENT_COUNTY_CLEANSED AS C
				ON	A.FIPS        = C.FIPS
				AND A.FISCAL_YEAR = C.YEAR
				LEFT OUTER JOIN
					BEA_GDP_COUNTY_CLEANSED	 AS D
				ON	A.FIPS        = D.FIPS
				AND A.FISCAL_YEAR = D.YEAR
WHERE			A.FISCAL_YEAR >= 2010
			AND A.FISCAL_YEAR <= 2015
;

/* Create Sample D */
SELECT			A.*                                              ,
				L_MANDATORY_AUDIT_SHARE = B.MANDATORY_AUDIT_SHARE
INTO			SMPL_D
FROM			SMPL_D_VARIABLES AS A
			LEFT OUTER JOIN
				SMPL_D_VARIABLES AS B
			ON	A.FIPS_ID = B.FIPS_ID
			AND	(A.FISCAL_YEAR - B.FISCAL_YEAR) = 1	
WHERE			B.TAXPAYERS_WHO_GIVE_SHARE IS NOT NULL	
			AND	A.MANDATORY_AUDIT_SHARE    IS NOT NULL
			AND	A.GDP_PER_CAPITA           IS NOT NULL
			AND	A.UNEMPLOYMENT_RATE        IS NOT NULL
			AND	A.ADJ_GROSS_INCOME         IS NOT NULL
;

/* **************************************************************************************** */
/* Sample E */

/* Code instrument for Sample E */
-- Charity-year sample for the crowding-out analysis: observations based on the long Form 990 available since 2008 and for charities with revenue above $200K 
SELECT			*
INTO			SAMPLE_CHARITY_YEAR_CROWDING
FROM			SAMPLE_CHARITY_YEAR
WHERE			DATASOURCE = 'GUIDESTAR_POST2008'
			AND	REVENUE_TOTAL > 200000
;

-- For each observation, produce additional combinations of each "simulated state" 
SELECT 						A.FISCAL_YEAR             ,
							A.EIN                     ,
							A.MAJORGROUP              ,
							A.ACTIVITY                ,
							A.PERIOD_END              ,
							A.REVENUE_TOTAL           ,
							A.RD_REV_LESS_GRANTS      ,
							A.RD_CONTRIB_LESS_GRANTS  ,
							A.RD_AVG_REV_3YR          ,
							B.STATE AS SIMULATED_STATE
INTO						SMPL_E_INST_01							
FROM						SAMPLE_CHARITY_YEAR_CROWDING AS A,
							(			
							SELECT DISTINCT	STATE
							FROM			SAMPLE_CHARITY_YEAR_CROWDING
							) AS B									
;							

-- For each simulated observation, determine if the chariy would be required to obtain an audit 
SELECT						A.*,                         
							MANDATORY_AUDIT_SIM = 
							CASE
								WHEN A.REVENUE_TOTAL          > B.REVENUE_TOTAL_CUTOFF 		 THEN 1
								WHEN A.RD_REV_LESS_GRANTS     > B.REV_LESS_GRANTS_CUTOFF 	 THEN 1
								WHEN A.RD_AVG_REV_3YR         > B.AVG_REV_3YR_CUTOFF 		 THEN 1
								WHEN A.RD_CONTRIB_LESS_GRANTS > B.CONTRIB_LESS_GRANTS_CUTOFF THEN 1
								ELSE 0
							END							
INTO						SMPL_E_INST_02							
FROM						SMPL_E_INST_01 AS A 
						LEFT OUTER JOIN
							JMP_AuditRegulations_Handcollected.DBO.STATE_AUDITREG_HANDCOLLECTED AS B
						ON	A.SIMULATED_STATE = B.STATE	
						AND	A.PERIOD_END      > B.DATE_EFFECTIVE
						AND A.PERIOD_END      < B.DATE_INEFFECTIVE
;					

-- Aggregate by activity-state-year level 
SELECT						A.*                                                                                     , 
							AUDIT_SHARE                = ISNULL(B.NB_AUDIT,0)                   / C.TOTAL_NUMBER    ,  
							MANDATORY_AUDIT_SHARE      = ISNULL(D.MANDATORY_AUDIT_NUMBER    ,0) / C.TOTAL_NUMBER    ,
							INST_MANDATORY_AUDIT_SHARE = ISNULL(F.MANDATORY_AUDIT_SIM_NUMBER,0) / E.TOTAL_SIM_NUMBER                       
INTO						SMPL_E_INST							
FROM			
							(
							SELECT DISTINCT	FISCAL_YEAR           ,
											STATE                 ,
											ACTIVITY              ,
											STATE_ACTIVITY_YEAR_ID,
											STATE_YEAR_ID         ,
											ACTIVITY_YEAR_ID      ,
											STATE_ACTIVITY_ID     ,
											STATE_ID              ,
											ACTIVITY_ID
							FROM			SAMPLE_CHARITY_YEAR_CROWDING 
							) AS A		
						
						LEFT OUTER JOIN
							(			
							SELECT			ACTIVITY			              ,
											STATE                             ,
											FISCAL_YEAR                       ,
											NB_AUDIT = CONVERT(FLOAT,COUNT(*)) 
							FROM			SAMPLE_CHARITY_YEAR_CROWDING
							WHERE			AUDIT = 1
							GROUP BY		ACTIVITY   ,
											STATE      ,
											FISCAL_YEAR	
							) AS B	
						ON	A.ACTIVITY	  = B.ACTIVITY
						AND	A.STATE       = B.STATE         
						AND	A.FISCAL_YEAR = B.FISCAL_YEAR
							
						LEFT OUTER JOIN
							(
							SELECT			STATE                                 ,
											ACTIVITY                              ,
											FISCAL_YEAR                           ,
											TOTAL_NUMBER = CONVERT(FLOAT,COUNT(*)) 
							FROM			SAMPLE_CHARITY_YEAR_CROWDING 
							GROUP BY		STATE      ,
											ACTIVITY   ,
											FISCAL_YEAR
							) AS C
						ON	A.STATE       = C.STATE
						AND	A.ACTIVITY    = C.ACTIVITY
						AND	A.FISCAL_YEAR = C.FISCAL_YEAR
						
						LEFT OUTER JOIN
							(
							SELECT			STATE                                           ,
											ACTIVITY                                        ,
											FISCAL_YEAR                                     ,
											MANDATORY_AUDIT_NUMBER = CONVERT(FLOAT,COUNT(*))
							FROM			SAMPLE_CHARITY_YEAR_CROWDING
							WHERE			RD_AUDIT_REQ = 1
							GROUP BY		STATE      ,
											ACTIVITY   ,
											FISCAL_YEAR
							) AS D
						ON	A.STATE       = D.STATE
						AND	A.ACTIVITY    = D.ACTIVITY
						AND	A.FISCAL_YEAR = D.FISCAL_YEAR	
						
						LEFT OUTER JOIN	
							(
							SELECT			SIMULATED_STATE                           ,
											ACTIVITY                                  ,
											FISCAL_YEAR                               ,
											TOTAL_SIM_NUMBER = CONVERT(FLOAT,COUNT(*)) 
							FROM			SMPL_E_INST_02
							GROUP BY		SIMULATED_STATE,
											ACTIVITY       ,
											FISCAL_YEAR
							) AS E
						ON	A.STATE       = E.SIMULATED_STATE
						AND	A.ACTIVITY    = E.ACTIVITY
						AND	A.FISCAL_YEAR = E.FISCAL_YEAR	
						
						LEFT OUTER JOIN 
							(
							SELECT			SIMULATED_STATE                                     ,
											ACTIVITY                                            ,
											FISCAL_YEAR                                         ,
											MANDATORY_AUDIT_SIM_NUMBER = CONVERT(FLOAT,COUNT(*))  
							FROM			SMPL_E_INST_02
							WHERE			MANDATORY_AUDIT_SIM = 1
							GROUP BY		SIMULATED_STATE,
											ACTIVITY       ,
											FISCAL_YEAR
							) AS F
						ON	A.STATE       = F.SIMULATED_STATE
						AND	A.ACTIVITY    = F.ACTIVITY
						AND	A.FISCAL_YEAR = F.FISCAL_YEAR
;						

-- Drop intermediary tables 
DROP TABLE SMPL_E_INST_01;
DROP TABLE SMPL_E_INST_02;

/* Code Donations_5K and merge variables */
SELECT 			A.*                                                         ,				
				B.DONATIONS_5K                                              ,    
				C.EXEMPT                                                    ,
				IN_SMPL_A = CASE WHEN C1.STATE IS NOT NULL THEN 1 ELSE 0 END
INTO			SMPL_E_VARIABLES						
FROM			SMPL_E_INST AS A	
			LEFT OUTER JOIN
				(
				SELECT			STATE                                            ,
								ACTIVITY                                         ,
								FISCAL_YEAR                                      ,
								DONATIONS_5K = LOG(1+SUM(CONVERT(FLOAT,SCHED_B)))                 
				FROM			SAMPLE_CHARITY_YEAR_CROWDING			
				GROUP BY		STATE      ,
								ACTIVITY   ,
								FISCAL_YEAR
				)  AS B
			ON	A.STATE       = B.STATE          
			AND	A.ACTIVITY    = B.ACTIVITY 
			AND	A.FISCAL_YEAR = B.FISCAL_YEAR 
			LEFT OUTER JOIN		
				(
				SELECT			*
				FROM			SMPL_A_VARIABLES
				WHERE			LN_TOTAL_DONATIONS      IS NOT NULL 
							AND CONCENTRATION_DONATIONS	IS NOT NULL 
							AND	FISCAL_YEAR             >= 2008
							AND	FISCAL_YEAR             <= 2015
							AND	EXEMPT                  = 0
				) AS C1
			ON	A.STATE       = C1.STATE          
			AND	A.ACTIVITY    = C1.ACTIVITY 
			AND	A.FISCAL_YEAR = C1.FISCAL_YEAR 
			LEFT OUTER JOIN
				SMPL_A_VARIABLES AS C
			ON	A.STATE       = C.STATE          
			AND	A.ACTIVITY    = C.ACTIVITY 
			AND	A.FISCAL_YEAR = C.FISCAL_YEAR 			
;

/* Create Sample E */
WITH SMPL_E_1 AS
(
SELECT			*
FROM			SMPL_E_VARIABLES
WHERE			FISCAL_YEAR >= 2008
			AND	FISCAL_YEAR <= 2015
			AND	EXEMPT       = 0
)
SELECT			A.*                                                        ,
				L_INST_MANDATORY_AUDIT_SHARE = B.INST_MANDATORY_AUDIT_SHARE,
				L_MANDATORY_AUDIT_SHARE      = B.MANDATORY_AUDIT_SHARE    
INTO			SMPL_E
FROM			SMPL_E_1 AS A
			LEFT OUTER JOIN
				SMPL_E_1 AS B
			ON	A.STATE    = B.STATE
			AND	A.ACTIVITY = B.ACTIVITY
			AND	(A.FISCAL_YEAR - B.FISCAL_YEAR) = 1
WHERE			A.IN_SMPL_A                  = 1
			AND	A.DONATIONS_5K			     IS NOT NULL
			AND	B.INST_MANDATORY_AUDIT_SHARE IS NOT NULL 
;

