Paid hours daily

1-- core.sona.paid_hours_daily
2-- Paid hours per day as Sona sends them to payroll, by location and pay type: the
3-- hours side of a labour cost report.
4--
5-- Grain: one row per local pay date x home org unit x pay type x entry type.
6-- Depends on: staging.sona.pay_lines, staging.sona.users, staging.sona.org_units
7--
8-- Pay lines carry a user but no location, so each line is placed on the user's
9-- administrative (home) org unit and dated in that unit's time zone. Someone who
10-- covers a shift elsewhere still counts at home here; use shift_coverage_daily for
11-- hours by where the work happened.
12--
13-- Cost is not computed: Sona returns hourly and annual rates in the same field, so
14-- multiplying hours by rate is only right for part of the lines. Join your own rate
15-- table if you need cost.
16
17SELECT
18    DATE(p.starts_at, COALESCE(o.timezone, 'UTC'))  AS pay_date,
19    u.home_org_unit_id                              AS org_unit_id,
20    ANY_VALUE(o.org_unit_name)                      AS org_unit_name,
21    p.pay_type,
22    p.entry_type,
23
24    COUNT(*)                                        AS pay_lines,
25    COUNT(DISTINCT p.user_id)                       AS people_paid,
26    ROUND(SUM(p.duration_minutes) / 60.0, 2)        AS paid_hours
27FROM {{staging.sona.pay_lines}} p
28LEFT JOIN {{staging.sona.users}} u
29  ON u.user_id = p.user_id
30LEFT JOIN {{staging.sona.org_units}} o
31  ON o.org_unit_id = u.home_org_unit_id
32GROUP BY pay_date, u.home_org_unit_id, p.pay_type, p.entry_type