Trial balance by month

1-- core.business_central.trial_balance_by_month
2-- Trial balance per month: for every company, account and month, the opening balance,
3-- the month's debits and credits, and the closing balance. Recreates Business Central's
4-- Trial Balance report (net change and balance at date) for each month end.
5--
6-- Grain: one row per company x month x account, from the company's first posting month
7-- to the current month (or the latest future-dated month), including months in which an account had no postings.
8-- Currency: the company's local currency (LCY). Sign: debit positive, credit negative.
9--
10-- Balances accumulate over every entry synced, which is what Business Central does:
11-- income statement accounts are reset only by the closing entries posted when a fiscal
12-- year is closed ("Close Income Statement"), and those entries are G/L entries like any
13-- other. With full history synced, closing balances of all accounts in a company sum to
14-- zero every month.
15
16WITH movements AS (
17    SELECT
18        company_id,
19        posting_month                      AS month,
20        account_id,
21        SUM(debit)                         AS period_debit,
22        SUM(credit)                        AS period_credit,
23        SUM(amount)                        AS period_movement
24    FROM {{core.business_central.general_ledger}}
25    WHERE posting_month IS NOT NULL
26    GROUP BY 1, 2, 3
27),
28
29company_months AS (
30    SELECT
31        company_id,
32        month
33    FROM (
34        SELECT company_id, MIN(month) AS first_month, MAX(month) AS last_month
35        FROM movements
36        GROUP BY 1
37    ),
38    UNNEST(GENERATE_DATE_ARRAY(
39        first_month,
40        -- Run to the current month, or further if entries are posted ahead.