View: dbo.Employee_W4_Settings_Pivot_View | |||
View definition | |||
CREATE VIEW dbo.Employee_W4_Settings_Pivot_View AS select e.employee_KEY , e.data_version , e.ste_state_code_KEY , e.additional_allowances , e.additional_amount , e.additional_amount_type , e.allowance_based_on_deductions_optional , e.basic_allowances , e.county_percent , e.dependent_allowances , e.dependent_exemptions , e.dependents , e.eic_spouse_w5 , e.eic_status , e.fed_tax_exempt , e.fica_tax_exempt , e.futa_tax_exempt , e.filing_status , fs.filing_status__description , e.full_futa_rate , e.full_time_student , e.head_of_household , e.low_income_tax_rates , e.new_hire_fica_credit , e.non_residential_alien , e.non_residential_exemption_certificate , e.non_residential_percentage , e.number_of_dependents , e.percent_federal , e.percent_state , e.personal_allowances , e.personal_blindness , e.personal_exemption , e.personal_exemptions , e.rate_table , e.regular_allowances , e.resident , e.spouse_blindness , e.spouse_works , e.state_tax_exempt , e.supplemental , e.total_allowance , e.total_dependents , e.total_exemption_amt , e.two_earner_percent , e.withholding_code , e.deductions , e.other_income , e.two_jobs , e.w4_2020 , e.claim_dependents from ( SELECT employee_KEY = EW4S.employee_KEY , ISNULL(w4.data_version, CAST(1 AS TINYINT)) AS data_version , ste_state_code_KEY = EW4S.ste_state_code_KEY , additional_allowances = max(case when w4.parameter_KEY = N'ADDITIONALALLOWANCES' then w4.parameter_value else null end) , additional_amount = max(case when w4.parameter_KEY = N'ADDITIONALAMOUNT' then w4.parameter_value else null end) , additional_amount_type = max(case when w4.parameter_KEY = N'ADDITIONALAMOUNTTYPE' then w4.parameter_value else null end) , allowance_based_on_deductions_optional = max(case when w4.parameter_KEY = N'ALLOWANCEBASEDONDEDUCTIONSOPTIONAL' then case w4.parameter_value when N'true' then N'T' when N'false' then N'F' else w4.parameter_value end else null end) , basic_allowances = max(case when w4.parameter_KEY = N'BASICALLOWANCES' then w4.parameter_value else null end) , county_percent = max(case when w4.parameter_KEY = N'COUNTYPERCENT' then w4.parameter_value else null end) , dependent_allowances = max(case when w4.parameter_KEY = N'DEPENDENTALLOWANCES' then w4.parameter_value else null end) , dependent_exemptions = max(case when w4.parameter_KEY = N'DEPENDENTEXEMPTIONS' then w4.parameter_value else null end) , dependents = max(case when w4.parameter_KEY = N'DEPENDENTS' then w4.parameter_value else null end) , eic_spouse_w5 = max(case when w4.parameter_KEY = N'EICSPOUSEW5' then w4.parameter_value else null end) , eic_status = max(case when w4.parameter_KEY = N'EICSTATUS' then w4.parameter_value else null end) , fed_tax_exempt = max(case when w4.parameter_KEY = N'FEDTAXEXEMPT' then w4.parameter_value else null end) , fica_tax_exempt = max(case when w4.parameter_KEY = N'FICATAXEXEMPT' then w4.parameter_value else null end) , futa_tax_exempt = max(case when w4.parameter_KEY = N'FUTATAXEXEMPT' then w4.parameter_value else null end) , filing_status = max(case when w4.parameter_KEY = N'FILINGSTATUS' then w4.parameter_value else null end) , full_futa_rate = max(case when w4.parameter_KEY = N'FULLFUTARATE' then w4.parameter_value else null end) , full_time_student = max(case when w4.parameter_KEY = N'FULLTIMESTUDENT' then case w4.parameter_value when N'true' then N'T' when N'false' then N'F' else w4.parameter_value end else null end) , head_of_household = max(case when w4.parameter_KEY = N'HEADOFHOUSEHOLD' then case w4.parameter_value when N'true' then N'T' when N'false' then N'F' else w4.parameter_value end else null end) , low_income_tax_rates = max(case when w4.parameter_KEY = N'LOWINCOMETAXRATES' then case w4.parameter_value when N'true' then N'T' when N'false' then N'F' else w4.parameter_value end else null end) , new_hire_fica_credit = max(case when w4.parameter_KEY = N'NEWHIREFICACREDIT' then w4.parameter_value else null end) , non_residential_alien = max(case when w4.parameter_KEY = N'NONRESIDENTALIEN' then w4.parameter_value else null end) , non_residential_exemption_certificate = max(case when w4.parameter_KEY = N'NONRESIDENTEXEMPTIONCERTIFICATE' then case w4.parameter_value when N'true' then N'T' when N'false' then N'F' else w4.parameter_value end else null end) , non_residential_percentage = max(case when w4.parameter_KEY = N'NONRESPERCENTAGE' then w4.parameter_value else null end) , number_of_dependents = max(case when w4.parameter_KEY = N'COMPLETEDEPENDENTS' then w4.parameter_value else null end) , percent_federal = max(case when w4.parameter_KEY = N'PERCENTFEDERAL' then w4.parameter_value else null end) , percent_state = max(case when w4.parameter_KEY = N'PERCENTSTATE' then w4.parameter_value else null end) , personal_allowances = max(case when w4.parameter_KEY = N'PERSONALALLOWANCES' then w4.parameter_value else null end) , personal_blindness = max(case when w4.parameter_KEY = N'PERSONALBLINDNESS' then case w4.parameter_value when N'true' then N'T' when N'false' then N'F' else w4.parameter_value end else null end) , personal_exemption = max(case when w4.parameter_KEY = N'PERSONALEXEMPTION' then case w4.parameter_value when N'true' then N'T' when N'false' then N'F' else w4.parameter_value end else null end) , personal_exemptions = max(case when w4.parameter_KEY = N'PERSONALEXEMPTIONS' then w4.parameter_value else null end) , rate_table = max(case when w4.parameter_KEY = N'RATETABLE' then w4.parameter_value else null end) , regular_allowances = max(case when w4.parameter_KEY = N'REGULARALLOWANCES' then w4.parameter_value else null end) , resident = max(case when w4.parameter_KEY = N'RESIDENT' then case w4.parameter_value when N'true' then N'T' when N'false' then N'F' else w4.parameter_value end else null end) , spouse_blindness = max(case when w4.parameter_KEY = N'SPOUSEBLINDNESS' then case w4.parameter_value when N'true' then N'T' when N'false' then N'F' else w4.parameter_value end else null end) , spouse_works = max(case when w4.parameter_KEY = N'SPOUSEWORKS' then case w4.parameter_value when N'true' then N'T' when N'false' then N'F' else w4.parameter_value end else null end) , state_tax_exempt = max(case when w4.parameter_KEY = N'STATETAXEXEMPT' then case w4.parameter_value when N'true' then N'T' when N'false' then N'F' else w4.parameter_value end else null end) , supplemental = max(case when w4.parameter_KEY = N'SUPPLEMENTAL' then case w4.parameter_value when N'true' then N'T' when N'false' then N'F' else w4.parameter_value end else null end) , total_allowance = max(case when w4.parameter_KEY = N'TOTALALLOWANCES' then w4.parameter_value else null end) , total_dependents = max(case when w4.parameter_KEY = N'TOTALDEPENDENTS' then w4.parameter_value else null end) , total_exemption_amt = max(case when w4.parameter_KEY = N'TOTALEXEMPTIONAMT' then w4.parameter_value else null end) , two_earner_percent = max(case when w4.parameter_KEY = N'TWOEARNERPERCENT' then case w4.parameter_value when N'true' then N'T' when N'false' then N'F' else w4.parameter_value end else null end) , withholding_code = max(case when w4.parameter_KEY = N'WITHHOLDINGCODE' then w4.parameter_value else null end) ,deductions = max(case when w4.parameter_KEY = N'DEDUCTIONS' then w4.parameter_value else null end) ,other_income = max(case when w4.parameter_KEY = N'OTHER_INCOME' then w4.parameter_value else null end) ,two_jobs = max(case when w4.parameter_KEY = N'TWO_JOBS' then case w4.parameter_value when N'true' then N'T' when N'false' then N'F' else w4.parameter_value end else null end) ,w4_2020 = max(case when w4.parameter_KEY = N'2020_W4' then case w4.parameter_value when N'true' then N'T' when N'false' then N'F' else w4.parameter_value end else null end) ,claim_dependents = max(case when w4.parameter_KEY = N'DEPENDENTS_AMT' then w4.parameter_value else null end) FROM Employee_W4_Setting AS EW4S left join dbo.Employee_W4_Setting_Parameter as w4 on w4.employee_w4_setting_KEY = EW4S.employee_w4_setting_KEY group by EW4S.employee_KEY , EW4S.ste_state_code_KEY , w4.data_version ) as e left join ( SELECT SSMPDI.ste_state_code_KEY, SSMPDI.data_version, SSMPDI.ste_state_misc_parameter_KEY , SSMPDI.[description] AS filing_status__description, SSMPDI.allowed_value_id, SSMP.parameter_KEY FROM dbo.Ste_State_Misc_Parameter_Droplist_Item AS SSMPDI JOIN dbo.STE_State_Misc_Parameter AS SSMP ON SSMPDI.allowed_value_id = SSMP.allowed_value_id where SSMP.parameter_KEY = N'FILINGSTATUS' ) as fs on fs.ste_state_code_KEY = e.ste_state_code_KEY AND fs.data_version = e.data_version and fs.ste_state_misc_parameter_KEY = e.filing_status collate Latin1_General_CI_AS ; | |||