Weekly budget vs actual

1-- core.fourth.weekly_budget_vs_actual
2-- Budgeted sales and labour against actuals, per budget week, location and
3-- department.
4-- Grain: budget week x location x department.
5--
6-- Weeks are Fourth's budget weeks (week_start to week_end inclusive). Actuals
7-- come from core.fourth.labour_vs_sales, summed over the days in the week, so the
8-- same caveats about payroll lines apply.
9
10WITH actuals AS (
11    SELECT
12        b.location_id,
13        b.department_id,
14        b.week_start,
15        SUM(a.actual_sales)  AS actual_sales,
16        SUM(a.labour_cost)   AS actual_labour_cost,
17        SUM(a.labour_hours)  AS actual_labour_hours
18    FROM {{staging.fourth.budget}} b
19    JOIN {{core.fourth.labour_vs_sales}} a
20      ON a.department_id = b.department_id
21     AND a.date BETWEEN b.week_start AND b.week_end
22    GROUP BY 1, 2, 3
23)
24
25SELECT
26    b.week_start,
27    b.week_end,
28    b.location_id,
29    b.location_name,
30    b.department_id,
31    b.department_name,
32    b.sales_budget,
33    ROUND(COALESCE(a.actual_sales, 0), 2)                         AS actual_sales,
34    ROUND(COALESCE(a.actual_sales, 0) - b.sales_budget, 2)        AS sales_vs_budget,
35    b.labour_budget_pct,
36    ROUND(b.labour_budget_amount, 2)                              AS labour_budget_amount,
37    ROUND(COALESCE(a.actual_labour_cost, 0), 2)                   AS actual_labour_cost,
38    ROUND(COALESCE(a.actual_labour_cost, 0) - b.labour_budget_amount, 2) AS labour_vs_budget,
39    SAFE_DIVIDE(a.actual_labour_cost, a.actual_sales)             AS actual_labour_pct_of_sales,
40    ROUND(COALESCE(a.actual_labour_hours, 0), 2)                  AS actual_labour_hours,