Profit and loss by month

1-- core.business_central.profit_and_loss_by_month
2-- Income statement by month, at account level with Business Central's account category
3-- (Income, Cost of Goods Sold, Expense) and subcategory alongside for roll-up in BI.
4--
5-- Grain: one row per company x month x account, income statement accounts only.
6-- Currency: the company's local currency (LCY).
7-- Sign: `amount` is credit positive, the way an income statement reads: income positive,
8-- costs negative, and SUM(amount) is the result for the period.
9--
10-- Closing entries. When a fiscal year is closed, Business Central posts entries that
11-- move every income statement balance to retained earnings, dated on the last day of the
12-- year. They would make that month's result zero, so list the document numbers your
13-- company used for them in `closing_documents` below and they are excluded. Business
14-- Central marks these entries with a closing date internally, but the API returns them
15-- with an ordinary posting date, so the document number is the reliable handle.
16
17WITH closing_documents AS (
18    -- One row per (company_id, document_number) used by "Close Income Statement".
19    -- Empty by default. Example:
20    --   SELECT 'your-company-id' AS company_id, 'CLOSE2025' AS document_number
21    SELECT CAST(NULL AS STRING) AS company_id, CAST(NULL AS STRING) AS document_number
22    FROM UNNEST([1])
23    WHERE FALSE
24)
25
26SELECT
27    gl.company_id,
28    gl.company_name,
29    gl.local_currency_code,
30    gl.posting_month                       AS month,
31    gl.account_category,
32    gl.account_subcategory,
33    gl.account_id,
34    gl.account_number,
35    gl.account_name,
36    SUM(gl.debit)                          AS debit,
37    SUM(gl.credit)                         AS credit,
38    SUM(gl.amount_credit_positive)         AS amount,
39    COUNT(*)                               AS entries
40FROM {{core.business_central.general_ledger}} gl