I have created a query that gets the running total from my general ledger by period. This works fine, however as soon as there are no transasctions in a period, my query will return noting in that period. I would like to add previous value if this is the case. I have tried joining with my dimension period table, but have not been succesful.
Example: Project 12345 GL:
project | amount | period |
---|---|---|
12345 | 10 | 202001 |
12345 | 10 | 202002 |
12345 | 10 | 202003 |
12345 | 10 | 202004 |
12345 | 10 | 202005 |
12345 | 10 | 202006 |
My code is the following:
select project, Period,
Sum( Sum (amount))
over (
partition by project
order by Period
ROWS UNBOUNDED PRECEDING) AS [RTamt]
from GL.table
group by project, period
This would return the following table
project | amount | period |
---|---|---|
12345 | 10 | 202001 |
12345 | 20 | 202002 |
12345 | 30 | 202003 |
12345 | 40 | 202004 |
12345 | 50 | 202005 |
12345 | 60 | 202006 |
I would like it to continue until today period like this:
project | amount | period |
---|---|---|
12345 | 10 | 202001 |
12345 | 20 | 202002 |
12345 | 30 | 202003 |
12345 | 40 | 202004 |
12345 | 50 | 202005 |
12345 | 60 | 202006 |
12345 | 60 | 202007 |
12345 | 60 | 202008 |
12345 | 60 | 202009 |
12345 | 60 | 202010 |
12345 | 60 | 202011 |
12345 | 60 | 202012 |
12345 | 60 | 202101 |
12345 | 60 | 202102 |
12345 | 60 | 202103 |
12345 | 60 | 202104 |
12345 | 60 | 202105 |
12345 | 60 | 202106 |
12345 | 60 | 202107 |
12345 | 60 | 202108 |
12345 | 60 | 202109 |
Is there an easy way to do this by joining a period table maybe?
Thanks so much!