Trial balance

1-- core.fortnox.trial_balance
2-- Trial balance per financial year (saldobalans): opening balance, the year's debits
3-- and credits, and closing balance for every account.
4--
5-- Grain: one row per financial year x account.
6-- Currency: SEK. Sign: debit positive, credit negative, so the closing balances of all
7-- accounts in a year sum to zero.
8--
9-- The opening balance comes from Fortnox itself (`balance_brought_forward` on the
10-- account for that year): it is zero for income statement accounts and carries the
11-- prior year's closing balance for balance sheet accounts once Fortnox has rolled the
12-- year over. `closing_balance` is computed here as opening + movement;
13-- `closing_balance_fortnox` is Fortnox's own figure, and `closing_balance_difference`
14-- should be zero (see tests/assert_fortnox__trial_balance_ties_to_fortnox.sql).
15--
16-- An account appears if it is in the chart for that year or has postings in it, so
17-- accounts with a balance and no movement are not lost.
18
19WITH movements AS (
20    SELECT
21        financial_year_id,
22        account_number,
23        SUM(debit)  AS period_debit,
24        SUM(credit) AS period_credit,
25        SUM(amount) AS period_movement
26    FROM {{core.fortnox.general_ledger}}
27    GROUP BY 1, 2
28),
29
30accounts_and_movements AS (
31    SELECT
32        COALESCE(a.financial_year_id, m.financial_year_id) AS financial_year_id,
33        COALESCE(a.account_number, m.account_number)       AS account_number,
34        a.account_name,
35        a.is_active,
36        COALESCE(a.opening_balance, 0)                     AS opening_balance,
37        COALESCE(m.period_debit, 0)                        AS period_debit,
38        COALESCE(m.period_credit, 0)                       AS period_credit,
39        COALESCE(m.period_movement, 0)                     AS period_movement,
40        a.closing_balance_fortnox