0

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!

2
  • 1
    What is period? int? string? why not date? Sep 7 at 14:40
  • it sure can be a data. right not its INT. If making this a date instead (i.e first in every month, then that is perfectly fine). @AaronBertrand
    – Clueless
    Sep 7 at 15:14
1

You can create a recursive CTE based on the highest period for any given project, then use a union to piece those two sets together. There is probably a slightly cleaner way to do this but I wanted to do as little to your existing query as possible (I just added a row number and slight formatting changes for my own readability).

DECLARE @d date = DATEFROMPARTS(YEAR(getdate()), MONTH(getdate()), 1);

;WITH src AS -- mostly your existing query
(
  SELECT project, 
         period,
         rn = ROW_NUMBER() OVER 
              (PARTITION BY project ORDER BY period DESC),
         amount = SUM(SUM(amount)) OVER 
                  (PARTITION BY project ORDER BY period
                   ROWS UNBOUNDED PRECEDING)
  FROM dbo.GLProject
  GROUP BY project, period
),
recur AS
(
  SELECT project, period = DATEADD(MONTH, 1, period), amount
  FROM src 
  WHERE rn = 1 AND period < @d
  UNION ALL
  SELECT project, DATEADD(MONTH, 1, period), amount
  FROM recur WHERE period < @d 
)
SELECT project, amount, period = CONVERT(char(6), period, 112)
FROM src
UNION ALL
SELECT project, amount, period = CONVERT(char(6), period, 112)
FROM recur
ORDER BY project, period;

Working db<>fiddle

6
  • Perfect! Just what I was looking for. Thank you!
    – Clueless
    Sep 9 at 6:07
  • After further review it looks like the code is missing periods in between and just filling out from last transaction. For instance if I where to add another row for project 12345 with period 202009 from my original question, it would miss the periods in between (202007 and 202008). Any easy fix regarding this? Thanks!
    – Clueless
    Sep 10 at 12:38
  • @Clueless cool, let's start a new question rather than shift the goal posts. :-) Sep 10 at 12:39
  • Thanks! I'll create a new post now!
    – Clueless
    Sep 10 at 12:40
  • @Clueless Can you start with the fiddle I created, and add the new sample data not included here, so it will be easier for answerers to start from that than to reverse engineer tabular data from your question. Sep 10 at 12:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.