Project budget

1-- core.harvest.project_budget
2-- Budget, spend and profitability per project, all time.
3-- Grain: project.
4--
5-- Two kinds of numbers sit side by side:
6--   budget_* columns come from Harvest's Project Budget report, in the unit of
7--     budget_by (hours or money) and, for monthly budgets, for the current month.
8--   everything else is summed from time entries, expenses and invoices over the
9--     project's whole life.
10-- revenue is the fixed fee for fixed-fee projects and billable time plus billable
11-- expenses otherwise, in the client's currency. cost is time cost (account
12-- currency) plus all expenses (client currency); when the two currencies differ,
13-- profit is not meaningful and is left NULL.
14
15WITH time_totals AS (
16    SELECT
17        project_id,
18        SUM(rounded_hours)                                                    AS hours,
19        SUM(IF(is_billable, rounded_hours, 0))                                AS billable_hours,
20        SUM(IF(is_billable, rounded_hours * COALESCE(billable_rate, 0), 0))   AS billable_amount,
21        SUM(hours * COALESCE(cost_rate, 0))                                   AS time_cost,
22        MIN(spent_date)                                                       AS first_entry_date,
23        MAX(spent_date)                                                       AS last_entry_date
24    FROM {{staging.harvest.time_entry}}
25    GROUP BY 1
26),
27
28expense_totals AS (
29    SELECT
30        project_id,
31        SUM(total_cost)                          AS expenses,
32        SUM(IF(is_billable, total_cost, 0))      AS billable_expenses
33    FROM {{staging.harvest.expense}}
34    GROUP BY 1
35),
36
37invoiced AS (
38    SELECT
39        l.project_id,
40        SUM(l.amount) AS invoiced_amount