View: dbo.Client_N_Client_Payroll_Service_View
View definition
-- This view takes a collection of checkboxes in the Client_Payroll_Information table and turns them into rows
-- that map to a view that defines types. The result is a row for each marked checkbox.
create view dbo.Client_N_Client_Payroll_Service_View as
select
UP.client_KEY
, case
when UP.client_payroll_service_column_name = N'is_client_self_service_enabled' and UP.is_enabled = 1 then 1
when UP.client_payroll_service_column_name = N'is_sbpr_client_access_enabled' and UP.is_enabled = 1 then 2
when UP.client_payroll_service_column_name = N'is_net_client_web_employee' and UP.is_enabled = 1 then 3
when UP.client_payroll_service_column_name = N'is_time_rack_enabled' and UP.is_enabled = 1 then 4
when UP.client_payroll_service_column_name = N'exclude_timerack_salary_employees' and UP.is_enabled = 1 then 5
else null
end
as client_payroll_service_KEY
from
(select
client_KEY
, is_client_self_service_enabled
, is_sbpr_client_access_enabled
, is_net_client_web_employee
, is_time_rack_enabled
, exclude_timerack_salary_employees
from dbo.Client_Payroll_Information
) P
unpivot
(
is_enabled for client_payroll_service_column_name in
(
is_client_self_service_enabled
, is_sbpr_client_access_enabled
, is_net_client_web_employee
, is_time_rack_enabled
, exclude_timerack_salary_employees)
) as UP
where UP.is_enabled = 1