Time report

1-- core.harvest.time_report
2-- Harvest's Time report: hours, billable hours, billable amount and cost.
3-- Grain: day x user x project x task.
4--
5-- Money follows Harvest's own conventions:
6--   billable_amount = rounded_hours x billable_rate, for billable entries only,
7--                     in the CLIENT's currency (client_currency)
8--   cost_amount     = hours x cost_rate, in the ACCOUNT's currency
9--                     (account_currency), because cost rates are set per person
10--                     in the account currency
11-- Do not add the two across clients with different currencies.
12--
13-- Running timers are included with the hours tracked so far.
14
15SELECT
16    t.spent_date,
17    t.user_id,
18    ANY_VALUE(t.user_name)                                          AS user_name,
19    ANY_VALUE(t.client_id)                                          AS client_id,
20    ANY_VALUE(t.client_name)                                        AS client_name,
21    t.project_id,
22    ANY_VALUE(t.project_name)                                       AS project_name,
23    ANY_VALUE(t.project_code)                                       AS project_code,
24    t.task_id,
25    ANY_VALUE(t.task_name)                                          AS task_name,
26    ANY_VALUE(t.client_currency)                                    AS client_currency,
27    ANY_VALUE(c.currency)                                           AS account_currency,
28    COUNT(*)                                                        AS time_entries,
29    ROUND(SUM(t.hours), 4)                                          AS hours,
30    ROUND(SUM(t.rounded_hours), 4)                                  AS rounded_hours,
31    ROUND(SUM(IF(t.is_billable, t.rounded_hours, 0)), 4)            AS billable_hours,
32    ROUND(SUM(IF(t.is_billable, 0, t.rounded_hours)), 4)            AS non_billable_hours,
33    ROUND(SUM(IF(t.is_billable, t.rounded_hours * COALESCE(t.billable_rate, 0), 0)), 2) AS billable_amount,
34    ROUND(SUM(IF(t.is_billable AND NOT t.is_billed, t.rounded_hours * COALESCE(t.billable_rate, 0), 0)), 2) AS uninvoiced_amount,
35    ROUND(SUM(t.hours * COALESCE(t.cost_rate, 0)), 2)               AS cost_amount,
36    COUNTIF(t.cost_rate IS NULL)                                    AS entries_without_cost_rate
37FROM {{staging.harvest.time_entry}} t
38CROSS JOIN {{staging.harvest.company}} c
39GROUP BY t.spent_date, t.user_id, t.project_id, t.task_id