Employee roster

1-- core.hibob.employee_roster
2-- One row per person: role, team, manager, employment terms, tenure and time off
3-- taken in the last 12 months. HiBob's People directory plus a few derived columns.
4-- Grain: employee (active and inactive).
5--
6-- Tenure is measured from the current start date to today, or to the termination
7-- date for people who have left. Time off counts approved hours on days in the
8-- last 365 days up to today.
9
10WITH recent_time_off AS (
11    SELECT
12        d.employee_id,
13        SUM(d.hours) AS approved_hours_off_last_12m
14    FROM {{staging.hibob.time_off_day}} d
15    JOIN {{staging.hibob.time_off_request}} r USING (request_id)
16    WHERE r.status = 'approved'
17      AND d.date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 364 DAY) AND CURRENT_DATE()
18    GROUP BY 1
19)
20
21SELECT
22    e.employee_id,
23    e.display_name,
24    e.email,
25    e.job_title,
26    e.department,
27    e.site,
28    e.manager_name,
29    e.is_manager,
30    e.employment_type,
31    e.fte,
32    e.status,
33    e.lifecycle_status,
34    e.start_date,
35    e.original_start_date,
36    IF(e.termination_date >= e.start_date, e.termination_date, NULL)           AS termination_date,
37    e.termination_type,
38    e.termination_reason,
39    ROUND(DATE_DIFF(
40        LEAST(CURRENT_DATE(), COALESCE(IF(e.termination_date >= e.start_date, e.termination_date, NULL), CURRENT_DATE())),