SQL YTD calculations adding periods without postings

I have a query which give me the YTD figures per company (client), account, IC and period for the periods in which financial postings have been performed. The table where I get this from is AAGCONSO which is a balance table of all the accounts, per client, period and IC.

``````SELECT        * FROM  (SELECT        client, account, IC, period, SUM(amount) OVER (PARTITION BY client, account, IC, LEFT(period, 4)
ORDER BY client, account, IC, period) AS YTD, ROW_NUMBER() OVER (PARTITION BY client, account, IC, period ORDER BY client, account, IC, period ASC) AS RowYTD FROM            (SELECT        t .client AS client, t .dim1 AS account, CASE WHEN t .dim2 IN ('10', '11', '30', '31', '32', '33', '34', '35', '36', '50', '60') THEN t .dim2 ELSE '' END AS IC, t .dim2, t .period AS period, SUM(t .amount)
AS amount, ROW_NUMBER() OVER (PARTITION BY t .client, t .dim1, LEFT(period, 4)
ORDER BY t .client, t .dim1, LEFT(period, 4) DESC) AS Row FROM            aagconso t WHERE        t .period >= 201853 AND client IN ('10', '11', '30', '31', '32', '33', '34', '35', '36', '50', '60') AND t .dim1 = '111110' GROUP BY t .client, t .dim1, t .dim2, period, t .amount HAVING        SUM(t .amount) <> 0) AS CTE2) X WHERE        RowYTD = 1 strong text ORDER BY 1 DESC Offset 0 Rows
``````

This gives me the YTD amounts in the following table

The totals are correct, however I want to have the periods in which no postings have been performed included with the YTD amount of previous period.

I have another table with all possible accounting periods

``````SELECT acc_period FROM dbo.PBAP_GL_PERIODS
``````

Which gives me this :

How do I get a full YTD table including the periods from table B for which there are no postings in table A? I probably need to use a cross apply or join but I really do not see where I get this ...

So for example Table A for account 111110 in client 10 gives me this

Client Account Period Amount RowYTD
10 111110 202012 1325925.000 1

And I want to have this

Client Account Period Amount RowYTD
10 111110 202000 0.000 1
10 111110 202001 0.000 1
10 111110 202002 0.000 1
10 111110 202003 0.000 1
10 111110 202004 0.000 1
10 111110 202005 0.000 1
10 111110 202006 0.000 1
10 111110 202007 0.000 1
10 111110 202008 0.000 1
10 111110 202009 0.000 1
10 111110 202010 0.000 1
10 111110 202011 0.000 1
10 111110 202012 1325925.000 1

Thanks in advance for any help!

• Please add content to make a minimal, reproducible example, like table DDL, sample data, and ideally a fiddle. Jan 29, 2021 at 16:41
• Hello, I am pretty new to this. Can you please explain me in detail what you need? how doe the fiddle work? Jan 29, 2021 at 16:47
• The link explains it in detail. Jan 29, 2021 at 16:49
• OK so I am creating my base table now in the SQL fiddle. How do you get access to that data? Jan 29, 2021 at 17:22
• sqlfiddle.com/#!18/0211c/2/0 Jan 29, 2021 at 17:22