Labour cost daily

1-- core.fourth.labour_cost_daily
2-- Labour hours and cost per day and department, split by Fourth's payroll type,
3-- pay part and status.
4-- Grain: date x department x payroll type x pay part x status.
5--
6-- Keep the type, pay part and status columns in view: they are Fourth's own
7-- labels and vary by account. Look at the distinct values before summing across
8-- them, for example to keep only approved lines or to leave out holiday pay.
9
10SELECT
11    p.date,
12    dl.location_id,
13    dl.location_name,
14    p.department_id,
15    ANY_VALUE(p.department_name)             AS department_name,
16    p.payroll_type,
17    p.pay_part,
18    p.status,
19    COUNT(DISTINCT p.employee_id)            AS employees,
20    SUM(p.hours)                            AS hours,
21    SUM(p.pay_value)                        AS labour_cost
22FROM {{staging.fourth.payroll}} p
23LEFT JOIN {{core.fourth.department_location}} dl USING (department_id)
24GROUP BY p.date, dl.location_id, dl.location_name, p.department_id, p.payroll_type, p.pay_part, p.status