1select
2 extract(
3 year
4 from
5 journal_date
6 ) as year
7 , extract(
8 month
9 from
10 journal_date
11 ) as month
12 , count(j.journal_id) as number_of_journal_entries
13 , sum(
14 case
15 when j.journal_id is not null then 1
16 else 0
17 end
18 ) as number_of_journal_lines
19from
20 {{raw.xero.journal}} j
21 -- Join with journal_line to count the number of journal lines for each journal entry
22 left join {{raw.xero.journal_line}} jl on j.journal_id = jl.journal_id
23group by
24 1
25 , 2
26order by
27 year desc
28 , month descExample output
+ ------+-------+-----------------------+---------------------+
| year | month | number_of_journal_entries | number_of_journal_lines | + ------+-------+-----------------------+---------------------+
| 2023 | 8 | 150 | 300 | | 2023 | 7 | 140 | 280 | | 2023 | 6 | 130 | 260 | | 2023 | 5 | 120 | 240 | | 2023 | 4 | 110 | 220 | + ------+-------+-----------------------+---------------------+By extracting and aggregating Xero journal data based on year and month, this SQL template aids in the streamlined reporting of accounting information. The report brings forth a clear picture of the number of journal entries and journal lines created each month, helping to track the accounting activities over time. It leverages SQL's EXTRACT function and conditional aggregation to yield a structured breakdown of the journal entries, providing a comprehensive view that can support better financial management and reporting.