Profit and loss by month

1-- core.netsuite.profit_and_loss_by_month
2-- NetSuite's Income Statement by month and account, built from the general
3-- ledger.
4--
5-- Grain: accounting book x subsidiary x month x account, income statement account
6-- types only, months with postings only.
7--
8-- Currency: the subsidiary's base currency. Keep subsidiary_id in the grain of
9-- any rollup, or translate to one currency first.
10--
11-- Signs:
12--   amount            as the report prints it: income positive on income accounts,
13--                     expenses positive on expense accounts
14--   net_income_impact credit - debit: income positive, expenses negative. A plain
15--                     SUM over rows is their contribution to net income, so Gross
16--                     Profit is the sum over the Income and Cost of Goods Sold
17--                     sections and Net Income the sum over everything.
18--
19-- Months are calendar months of the transaction date; see trial_balance.sql for
20-- how that differs from NetSuite's posting periods.
21
22SELECT
23    netsuite_account,
24    accounting_book_id,
25    subsidiary_id,
26    transaction_month                      AS month,
27    account_id,
28    ANY_VALUE(account_number)              AS account_number,
29    ANY_VALUE(account_name)                AS account_name,
30    ANY_VALUE(account_type_id)             AS account_type_id,
31    ANY_VALUE(account_type_name)           AS account_type_name,
32    CASE ANY_VALUE(account_type_id)
33        WHEN 'Income'     THEN 'Income'
34        WHEN 'COGS'       THEN 'Cost of Goods Sold'
35        WHEN 'Expense'    THEN 'Expenses'
36        WHEN 'OthIncome'  THEN 'Other Income'
37        WHEN 'OthExpense' THEN 'Other Expenses'
38        ELSE 'Other'
39    END                                    AS section,
40    CASE ANY_VALUE(account_type_id)