View: dbo.Employee_View
View definition
CREATE VIEW dbo.Employee_View
AS
SELECT employee_KEY = E.employee_KEY ,
client_KEY = E.client_KEY ,
employee_id = E.employee_id ,
social_security_number = E.social_security_number ,
first_name = E.first_name ,
middle_name = E.middle_name ,
last_name = E.last_name ,
name_suffix = E.name_suffix ,
contact_KEY = E.contact_KEY ,
date_of_birth = E.date_of_birth ,
employee_events.date_hired ,
employee_events.date_inactive ,
employee_events.date_last_raise ,
marital_status_type_KEY = E.marital_status_type_KEY ,
gender_type_KEY = E.gender_type_KEY ,
race_type_KEY = E.race_type_KEY ,
is_officer = E.is_officer ,
is_probationary = E.is_probationary ,
is_seasonal = E.is_seasonal ,
is_family_of_owner = E.is_family_of_owner ,
date_ssn_verified = E.date_ssn_verified ,
w2_statutory_employee = E.w2_statutory_employee ,
ste_location_code = E.ste_location_code ,
primary__client_pay_schedule_KEY = E.primary__client_pay_schedule_KEY ,
alternate__client_pay_schedule_KEY = E.alternate__client_pay_schedule_KEY ,
is_paycard_recipient = E.is_paycard_recipient ,
direct_deposit_method_KEY = E.direct_deposit_method_KEY ,
direct_deposit_amount_percent = E.direct_deposit_amount_percent ,
direct_deposit_distribution_method_KEY = E.direct_deposit_distribution_method_KEY ,
use_alternate_direct_deposit_distribution = E.use_alternate_direct_deposit_distribution ,
distribute_pay_by_percentage = E.distribute_pay_by_percentage ,
date_new_hire_reported = E.date_new_hire_reported ,
employee_id_sortable = E.employee_id_sortable ,
record_status_KEY = E.record_status_KEY ,
employee_type_KEY = E.employee_type_KEY ,
use_backup_withholding_rate = E.use_backup_withholding_rate ,
current__employee_earning_type_KEY = E.current__employee_earning_type_KEY ,
is_webemployee_portal_disabled = E.is_webemployee_portal_disabled ,
is_webemployee_portal_activated = E.is_webemployee_portal_activated ,
resend_employee_self_service_welcome_email = E.resend_employee_self_service_welcome_email ,
transmit_is_webemployee_portal_disabled = E.transmit_is_webemployee_portal_disabled ,
export__quick_books_payee_KEY = E.export__quick_books_payee_KEY ,
has_health_care = E.has_health_care ,
affordable_care_act_employee_status_KEY = E.affordable_care_act_employee_status_KEY,
employee_version = E.employee_version,
employee_force_new_version_toggle = E.employee_force_new_version_toggle,
forced_webemployee_push_version = E.forced_webemployee_push_version,
covid_19_defer_employee_social_security = E.covid_19_defer_employee_social_security,
covid_19_exempt_employee_from_employee_retention_credit = E.covid_19_exempt_employee_from_employee_retention_credit,
description = ( RTRIM(LTRIM(last_name))
+ CASE WHEN ( ( LEN(first_name) > 0 )
OR ( LEN(middle_name) > 0 )
OR ( LEN(name_suffix) > 0 )
) THEN N','
ELSE N''
END
+ CASE WHEN ( LEN(first_name) > 0 ) THEN N' '
ELSE N''
END + RTRIM(LTRIM(first_name))
+ CASE WHEN ( LEN(middle_name) > 0 ) THEN N' '
ELSE N''
END + RTRIM(LTRIM(middle_name))
+ CASE WHEN ( LEN(name_suffix) > 0 ) THEN N' '
ELSE N''
END + RTRIM(LTRIM(name_suffix)) ) COLLATE Latin1_General_CI_AS ,
employee_multi_state_income_tax_calculation_method_KEY = E.employee_multi_state_income_tax_calculation_method_KEY ,
is_w2_retirement_plan = E.is_w2_retirement_plan ,
is_part_time = E.is_part_time ,
employee_primary_work_location_payroll_department_KEY = EPWLPD.employee_primary_work_location_payroll_department_KEY
--Column added to get the status of the employee based on his latest inactive date and hire date.
-- Determining the active status is kind of complicated. Here's a truth table describing what we're trying to do
-- ( b=blank, x=has a value, H=the hired date, I=the inactive date):
-- Scenario Hired_date Inactive_date Active? Notes
-- 1 b b yes no dates, assume we're an active employee
-- 2 b x I>today we're active as long as our inactive is in the future
-- 3 x b yes Even future hired dates are considered active. WI 513823
-- 4 x x see below There are a couple of different scenarios we need to look at in this situation.
-- 4.1 H < I I>today we know when we're hired and fired, so we're active as long as we haven't stopped working
-- 4.2 H > I yes we've been hired but not fired, so we're active
-- 4.3 H = I no Based on work item 142482
,
active_status_KEY = CASE WHEN ( date_inactive IS NULL
OR --covers scenario 1 & 3
( date_hired IS NULL
AND date_inactive > GETDATE()
)
OR --covers scenario 2
( date_hired IS NOT NULL
AND date_hired > date_inactive
)
OR --covers scenario 4.2
( date_hired IS NOT NULL
AND date_hired < date_inactive
AND date_inactive > GETDATE()
)
) --covers scenario 4.1
THEN 1
ELSE 2
END
FROM dbo.Employee AS E
LEFT OUTER JOIN ( SELECT employee_KEY ,
[1] AS date_last_raise ,
[2] AS date_inactive ,
[3] AS date_hired
FROM ( SELECT employee_KEY ,
event_date ,
employee_event_type_KEY
FROM dbo.Employee_Event AS ev
) AS BASE PIVOT ( MAX(event_date) FOR employee_event_type_KEY IN ( [1],
[2], [3] ) ) AS PVT
) AS employee_events ON E.employee_KEY = employee_events.employee_KEY
LEFT OUTER JOIN dbo.Employee_Primary_Work_Location_Payroll_Department_View EPWLPD ON EPWLPD.employee_KEY = E.employee_KEY
;