View: dbo.Sui_Items_List_By_Employee_Client_View
View definition
-- this view is used to return a list of an employee's SUI state abbreviations
create view dbo.Sui_Items_List_By_Employee_Client_View
as
(
-- by joining to the Client_Tax_Item table, it is possible to get more than one
-- record returned per Employee_Tax_Jurisdiction, we use the distinct to limit
-- this to one record per Employee_Tax_Jurisdiction
select DISTINCT
ETJ.employee_tax_jurisdiction_KEY as sui_items_list_by_employee_client_KEY
, ETJ.employee_KEY
, TJ.client_KEY
, SSC.postal_code as abbreviation
from
dbo.Employee_Tax_Jurisdiction as ETJ -- start from the Employee_Tax_Jurisdiction table
inner join
dbo.Tax_Jurisdiction as TJ
on
TJ.tax_jurisdiction_KEY = ETJ.tax_jurisdiction_KEY
and
tax_jurisdiction_type_KEY = N'State'
inner join
dbo.Client_Tax_Item as CTI
on
CTI.tax_jurisdiction_KEY = TJ.tax_jurisdiction_KEY
and
CTI.ste_tax_code LIKE N'%SUI-%'
inner join
dbo.Ste_State_Code as SSC
on
SSC.ste_state_code_KEY = TJ.ste_state_code_KEY
)