View: dbo.Employee_View

View definition

CREATE VIEW dbo.Employee_View AS SELECT E.employee_KEY , E.client_KEY , E.employee_id , E.social_security_number , E.first_name , E.middle_name , E.last_name , E.name_suffix , E.contact_KEY , E.date_of_birth , employee_events.date_hired , employee_events.date_inactive , employee_events.date_last_raise , E.marital_status_type_KEY , E.gender_type_KEY , E.race_type_KEY , E.is_officer , E.is_probationary , E.is_seasonal , E.is_family_of_owner , E.date_ssn_verified , E.w2_statutory_employee , E.ste_location_code , E.primary__client_pay_schedule_KEY , E.alternate__client_pay_schedule_KEY , E.is_paycard_recipient , E.direct_deposit_method_KEY , E.direct_deposit_amount_percent , E.direct_deposit_distribution_method_KEY , E.use_alternate_direct_deposit_distribution , E.distribute_pay_by_percentage , E.date_new_hire_reported , E.employee_id_sortable , E.record_status_KEY , E.employee_type_KEY , E.use_backup_withholding_rate , E.current__employee_earning_type_KEY , E.is_webemployee_portal_disabled , E.is_webemployee_portal_activated , E.resend_employee_self_service_welcome_email , E.transmit_is_webemployee_portal_disabled , E.export__quick_books_payee_KEY , E.has_health_care , E.affordable_care_act_employee_status_KEY , E.employee_version , E.employee_force_new_version_toggle , E.forced_webemployee_push_version , E.covid_19_defer_employee_social_security , E.covid_19_exempt_employee_from_employee_retention_credit , E.first__treasury_tipped_occupation_code_KEY , E.second__treasury_tipped_occupation_code_KEY , ( 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 AS description , E.employee_multi_state_income_tax_calculation_method_KEY , E.is_w2_retirement_plan , E.is_part_time , 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 , 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 AS active_status_KEY 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 ;