Absence daily

1-- core.sona.absence_daily
2-- Who was off, and why, per day: absence days by type and location, with sickness
3-- and holiday split out. The basis for absence rate and sickness trend reporting.
4--
5-- Grain: one row per absence date x home org unit x absence type x day status.
6-- Depends on: staging.sona.absence_days, staging.sona.absences,
7--             staging.sona.absence_types, staging.sona.users
8--
9-- People are placed on their administrative (home) org unit, which is where Sona
10-- holds their record, not on wherever they would have worked that day.
11--
12-- day_status is Sona's status for each absence day and is part of the grain rather
13-- than filtered, because its values are not documented publicly. Look at the
14-- distinct values in your data and filter in the analytics layer (for example to
15-- approved days only) once you know them.
16
17SELECT
18    d.absence_date,
19    u.home_org_unit_id                          AS org_unit_id,
20    ANY_VALUE(o.org_unit_name)                  AS org_unit_name,
21    a.absence_type_id,
22    ANY_VALUE(t.absence_type_name)              AS absence_type_name,
23    ANY_VALUE(t.is_sickness)                    AS is_sickness,
24    ANY_VALUE(t.is_holiday)                     AS is_holiday,
25    d.day_status,
26
27    COUNT(*)                                    AS absence_days,
28    COUNT(DISTINCT a.user_id)                   AS people_absent,
29    COUNTIF(a.is_paid)                          AS paid_absence_days,
30    COUNTIF(t.is_sickness AND a.sick_note_provided) AS sickness_days_with_sick_note
31FROM {{staging.sona.absence_days}} d
32JOIN {{staging.sona.absences}} a
33  ON a.absence_id = d.absence_id
34LEFT JOIN {{staging.sona.absence_types}} t
35  ON t.absence_type_id = a.absence_type_id
36LEFT JOIN {{staging.sona.users}} u
37  ON u.user_id = a.user_id
38LEFT JOIN {{staging.sona.org_units}} o
39  ON o.org_unit_id = u.home_org_unit_id
40GROUP BY d.absence_date, u.home_org_unit_id, a.absence_type_id, d.day_status