Profit and loss by month

1-- core.quickbooks.profit_and_loss_by_month
2-- QuickBooks' Profit and Loss report, by month and account, built from the
3-- general ledger.
4--
5-- Grain: company x month x account, income and expense accounts only, months with
6-- postings only.
7--
8-- Currency: the company's home currency. Month: the transaction date, on the
9-- accrual basis (the Journal report is accrual; a cash-basis P&L cannot be built
10-- from it).
11--
12-- Signs:
13--   amount            as the report prints it: income positive on income
14--                     accounts, expenses positive on expense accounts
15--   net_income_impact credit - debit: income positive, expenses negative. A plain
16--                     SUM over any set of rows is that set's contribution to net
17--                     income, so Gross Profit is SUM where section is Income or
18--                     Cost of Goods Sold, and Net Income is SUM over everything.
19--
20-- section follows the report's layout and comes from the account type. Accounts
21-- whose type is not one of the five P&L types fall back to the classification.
22
23SELECT
24    quickbooks_company,
25    transaction_month                                   AS month,
26    account_id,
27    ANY_VALUE(account_name)                             AS account_name,
28    ANY_VALUE(account_full_name)                        AS account_full_name,
29    ANY_VALUE(classification)                           AS classification,
30    ANY_VALUE(account_type)                             AS account_type,
31    CASE ANY_VALUE(account_type)
32        WHEN 'Income'             THEN 'Income'
33        WHEN 'Cost of Goods Sold' THEN 'Cost of Goods Sold'
34        WHEN 'Expense'            THEN 'Expenses'
35        WHEN 'Other Income'       THEN 'Other Income'
36        WHEN 'Other Expense'      THEN 'Other Expenses'
37        ELSE IF(ANY_VALUE(classification) = 'Revenue', 'Income', 'Expenses')
38    END                                                 AS section,
39    CASE ANY_VALUE(account_type)
40        WHEN 'Income'             THEN 1