Trial balance

1-- core.quickbooks.trial_balance
2-- Month-end trial balance per account, built from the general ledger.
3--
4-- Grain: company x month x account, from the account's first posting to the
5-- current month, so an account with no activity in a month still carries its
6-- balance. Plus one synthetic row per company and month, account_id NULL, that
7-- holds prior fiscal years' net income (see below).
8--
9-- Currency: the company's home currency.
10--
11-- Columns, all on the debit-positive convention (debit - credit):
12--   period_debit, period_credit, period_net_debit   activity within the month
13--   closing_net_debit       cumulative since the first transaction
14--   fiscal_ytd_net_debit    cumulative since the start of the fiscal year, using
15--                           the fiscal year start month from the company settings
16--   trial_balance_net_debit what QuickBooks' Trial Balance report shows: the
17--                           cumulative balance for balance sheet accounts, the
18--                           fiscal year-to-date amount for income and expense
19--                           accounts
20--   trial_balance_debit / trial_balance_credit   the same, split into the two
21--                           report columns
22--
23-- QuickBooks does not post a year-end closing entry. Its reports fold prior
24-- fiscal years' net income into Retained Earnings on the fly. The row with
25-- account_id NULL carries that amount, so trial_balance_net_debit sums to zero for
26-- every company and month, the same way the report's debit and credit totals agree.
27-- In the QuickBooks UI the amount is shown on the Retained Earnings line itself.
28
29WITH company AS (
30    SELECT quickbooks_company, fiscal_year_start_month
31    FROM {{staging.quickbooks.company}}
32),
33
34monthly AS (
35    SELECT
36        quickbooks_company,
37        transaction_month AS month,
38        account_id,
39        SUM(debit)        AS period_debit,
40        SUM(credit)       AS period_credit