Invoice aging

1-- core.harvest.invoice_aging
2-- Every sent invoice with what is still owed, how late it is and how long it
3-- took to get paid. Harvest's Outstanding Invoices report plus payment speed.
4-- Grain: invoice. Drafts are excluded.
5--
6-- Aging is measured against CURRENT_DATE() (UTC) and the invoice due date.
7-- Amounts are in each invoice's own currency: group by currency before summing.
8-- days_to_pay uses paid_date, which Harvest sets when the invoice is fully paid.
9
10SELECT
11    i.invoice_id,
12    i.invoice_number,
13    i.client_id,
14    i.client_name,
15    i.state,
16    i.currency,
17    i.issue_date,
18    i.due_date,
19    i.paid_date,
20    i.amount,
21    i.tax_amount,
22    i.due_amount,
23    i.amount - i.due_amount                                      AS paid_amount,
24    IF(i.state = 'open', GREATEST(DATE_DIFF(CURRENT_DATE(), i.due_date, DAY), 0), NULL) AS days_overdue,
25    CASE
26        WHEN i.state != 'open' OR i.due_amount <= 0          THEN NULL
27        WHEN CURRENT_DATE() <= i.due_date                    THEN 'not_due'
28        WHEN DATE_DIFF(CURRENT_DATE(), i.due_date, DAY) <= 30 THEN '1_30'
29        WHEN DATE_DIFF(CURRENT_DATE(), i.due_date, DAY) <= 60 THEN '31_60'
30        WHEN DATE_DIFF(CURRENT_DATE(), i.due_date, DAY) <= 90 THEN '61_90'
31        ELSE 'over_90'
32    END                                                          AS aging_bucket,
33    IF(i.state = 'paid', DATE_DIFF(i.paid_date, i.issue_date, DAY), NULL) AS days_to_pay,
34    IF(i.state = 'paid', i.paid_date > i.due_date, NULL)         AS paid_late
35FROM {{staging.harvest.invoice}} i
36WHERE i.state != 'draft'