Labor by job

1-- core.toast.labor_by_job
2-- Toast's Labor Summary by job: hours and wage cost per business day.
3-- Grain: business date x restaurant x job.
4--
5-- Cost is hours x the hourly wage on each time entry:
6--   regular_cost  = regular_hours x hourly_wage
7--   overtime_cost = overtime_hours x hourly_wage x 1.5
8-- The 1.5 overtime multiplier is the US default. Toast lets you change it per
9-- location; change it here if yours differs. Salaried staff and entries without a
10-- wage add hours but no cost: entries_without_wage counts them.
11-- Jobs marked "exclude from reporting" in Toast are kept and flagged.
12
13SELECT
14    t.business_date,
15    t.restaurant_id,
16    t.job_id,
17    ANY_VALUE(j.job_title)                                         AS job_title,
18    ANY_VALUE(j.is_tipped)                                         AS is_tipped,
19    ANY_VALUE(j.exclude_from_reporting)                            AS exclude_from_reporting,
20    COUNT(DISTINCT t.employee_id)                                  AS employees,
21    COUNT(*)                                                       AS time_entries,
22    ROUND(SUM(t.regular_hours), 2)                                 AS regular_hours,
23    ROUND(SUM(t.overtime_hours), 2)                                AS overtime_hours,
24    ROUND(SUM(t.regular_hours + t.overtime_hours), 2)              AS total_hours,
25    ROUND(SUM(t.regular_hours * COALESCE(t.hourly_wage, 0)), 2)    AS regular_cost,
26    ROUND(SUM(t.overtime_hours * COALESCE(t.hourly_wage, 0) * 1.5), 2) AS overtime_cost,
27    ROUND(SUM(t.regular_hours * COALESCE(t.hourly_wage, 0)
28            + t.overtime_hours * COALESCE(t.hourly_wage, 0) * 1.5), 2) AS labor_cost,
29    ROUND(SUM(t.declared_cash_tips + t.non_cash_tips), 2)          AS tips,
30    COUNTIF(t.hourly_wage IS NULL OR t.hourly_wage = 0)            AS entries_without_wage,
31    COUNTIF(t.was_auto_clocked_out)                                AS auto_clocked_out_entries
32FROM {{staging.toast.time_entry}} t
33LEFT JOIN {{staging.toast.job}} j
34       ON j.job_id = t.job_id AND j.restaurant_id = t.restaurant_id
35WHERE t.business_date IS NOT NULL
36GROUP BY t.business_date, t.restaurant_id, t.job_id