0

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

enter image description here

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 : enter image description here

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!

Improve this question
6
  • Please add content to make a minimal, reproducible example, like table DDL, sample data, and ideally a fiddle.
    – LowlyDBA - John M
    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?
    – Katrien
    Jan 29, 2021 at 16:47
  • The link explains it in detail.
    – LowlyDBA - John M
    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?
    – Katrien
    Jan 29, 2021 at 17:22
  • sqlfiddle.com/#!18/0211c/2/0
    – Katrien
    Jan 29, 2021 at 17:22

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.