View: dbo.Client_Processor_Information_View
View definition
-- This view surfaces the client key, active status key, and the primary, backup, reviewer staff keys.
-- The active status key is set based on the value for date_left. This view is the read source for
-- the Client Processor Information entity.
create view dbo.Client_Processor_Information_View
as
(
select
client_KEY = c.client_KEY
, primary_processor__staff_KEY = cpi.primary_processor__staff_KEY
, backup_processor__staff_KEY = cpi.backup_processor__staff_KEY
, reviewer__staff_KEY = cpi.reviewer__staff_KEY
, active_status_KEY = CASE WHEN c.date_left is not null
and c.date_left <= getdate()
THEN 2
ELSE 1
END
from dbo.Client as c
inner join dbo.Client_Payroll_Information as cpi
on c.client_KEY = cpi.client_KEY
)
;