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 ) | |||