period date not null,

account int not null,

companyType varchar(255) not null,

amount int not null default 0

);

('2022-01-01',11111,'Internal',100),

('2022-02-01',11111,'Internal',200),

('2022-06-01',11111,'Internal',300),

('2022-10-01',11111,'Internal',800),

('2022-10-01',11111,'External',100),

('2022-10-01',11111,'External',300),

('2022-12-01',11111,'Internal',100),

('2022-01-01',22222,'External',20),

('2022-06-01',22222,'External',50),

('2022-12-01',22222,'External',30);

SELECT * FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) AS m(m)

),

accounts(account) AS (

SELECT DISTINCT account FROM test

),

companyTypes(companyType) AS (

SELECT DISTINCT companyType FROM test

),

periods(period) AS (

SELECT DATEFROMPARTS(2022, month, 01) FROM months WHERE NOT EXISTS (

SELECT 1 FROM test WHERE MONTH(period) = month

) UNION ALL (SELECT DISTINCT period FROM Test)

)

SELECT p.period, a.account, ct.companyType, SUM(COALESCE(t.amount, 0))

FROM periods p

CROSS JOIN accounts as a

CROSS JOIN companyTypes as ct

LEFT JOIN test as t ON t.period = p.period AND t.account = a.account AND t.companyType = ct.companyType

GROUP BY p.period, a.account, ct.companyType

ORDER BY a.account, ct.companyType, p.period