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
;