View: dbo.Client_N_Client_Service_Type_For_Filters_View
View definition
-- This view turns client payroll and client payroll setup into essentially
-- mutually exclusive options. If a client has client payroll, then it will
-- client payroll setup. This means that any client with both services will
-- show up when filtering for the setup service. The users don't think of the
-- setup service as a part of full payroll - it's either setup only or the
-- full service.
--
-- So this view hides the setup service on clients that have payroll.
-- That means clients with both services (according to the database) will not
-- appear in the results when filtering for setup.
create view dbo.Client_N_Client_Service_Type_For_Filters_View
as
select
client_n_client_service_type_KEY
, client_KEY
, client_service_type_KEY
from dbo.Client_N_Client_Service_Type CNCST
where
client_service_type_KEY <> 9 -- Client Payroll Setup Only
or
(
CNCST.client_service_type_KEY = 9 -- Client Payroll Setup Only
and
CNCST.client_KEY not in
(
select
client_KEY
from dbo.Client_N_Client_Service_Type
where client_service_type_KEY = 5 -- Client Payroll
)
)