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 ; | |||