Trial balance by month

1-- core.netvisor.trial_balance_by_month
2-- Trial balance per month (saldoluettelo): for every account and month, the opening
3-- balance, the month's debits and credits, and the closing balance.
4--
5-- Grain: one row per month x account, from the first month with a synced voucher to the
6-- current month (or the latest future-dated month), including months in which an
7-- account had no postings.
8-- Currency: the company's accounting currency (EUR). Sign: debit positive, credit
9-- negative.
10--
11-- How balances accumulate differs by statement:
12--   balance sheet accounts (1xxx, 2xxx) accumulate over everything synced. They match
13--     Netvisor only if the synced ledger reaches back to the company's first entries or
14--     includes its opening balance voucher; see the README.
15--   income statement accounts (3xxx-9xxx) accumulate within the fiscal year and restart
16--     at its first month. Set `fiscal_year_start_month` below if your fiscal year does
17--     not start in January.
18-- Because prior years' results are not booked to equity in the ledger, the closing
19-- balances of all accounts in a month do not sum to zero; the period movements do.
20
21WITH settings AS (
22    SELECT 1 AS fiscal_year_start_month   -- 1 = January. Change for a broken fiscal year.
23),
24
25movements AS (
26    SELECT
27        voucher_month                     AS month,
28        account_number,
29        SUM(debit)                        AS period_debit,
30        SUM(credit)                       AS period_credit,
31        SUM(amount)                       AS period_movement
32    FROM {{core.netvisor.general_ledger}}
33    WHERE voucher_month IS NOT NULL
34    GROUP BY 1, 2
35),
36
37months AS (
38    SELECT month
39    FROM UNNEST(GENERATE_DATE_ARRAY(
40        (SELECT MIN(month) FROM movements),