Weekly utilization

1-- core.harvest.weekly_utilization
2-- Hours against capacity per person per week: Harvest's Team report and
3-- utilization numbers.
4-- Grain: week x user.
5--
6-- Weeks start on the day set in your Harvest account (company.week_start_day),
7-- so they line up with Harvest's own weekly views. Capacity is the user's CURRENT
8-- weekly capacity; Harvest does not keep a history of it, so a change to someone's
9-- capacity restates past weeks.
10--
11-- A person with no time tracked in a week has no row for that week. Utilization is
12-- therefore only shown for weeks with tracked time; build a calendar spine if you
13-- need zero weeks to show.
14
15WITH settings AS (
16    SELECT
17        CASE LOWER(week_start_day)
18            WHEN 'sunday'   THEN 1
19            WHEN 'saturday' THEN 7
20            ELSE 2  -- Monday, Harvest's default
21        END AS week_start_dow
22    FROM {{staging.harvest.company}}
23),
24
25entries AS (
26    SELECT
27        DATE_SUB(t.spent_date, INTERVAL MOD(EXTRACT(DAYOFWEEK FROM t.spent_date) - s.week_start_dow + 7, 7) DAY) AS week_start,
28        t.user_id,
29        t.rounded_hours,
30        t.is_billable
31    FROM {{staging.harvest.time_entry}} t
32    CROSS JOIN settings s
33)
34
35SELECT
36    e.week_start,
37    e.user_id,
38    u.user_name,
39    u.is_contractor,
40    u.is_active,