3

With a lof of help from the community I have been able to create a query that automatically adds new rows for project with accumulated number from previous row if there are no transactions. Unfortunately I came across on thing I would like to change. If there are no transactions in periods in between periods, those periods will be missing. I would like to fill those as well.

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
12345 10 202009

My code is the following:

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;

I would like it to fill in the missing periods 202007 and 202008 as well. 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 70 202009
12345 70 202010
12345 70 202011
12345 70 202012
12345 70 202101
12345 70 202102
12345 70 202103
12345 70 202104
12345 70 202105
12345 70 202106
12345 70 202107
12345 70 202108
12345 70 202109

Any ideas on what to add to my query to solve this?

Fiddle for testing here: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=615712cf89113c3a912268ed603fd2b4

Thanks so much!

5

You probably won't believe me, but adding the requirement of filling in missing gaps actually drove a simpler solution.

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

;WITH total_range AS 
(
  SELECT project, period = MIN(period)
    FROM dbo.GLProject
    GROUP BY project
  UNION ALL 
  SELECT project, DATEADD(MONTH, 1, period)
  FROM total_range 
  WHERE period < @d
)
SELECT 
  tr.project, 
  amount = SUM(COALESCE(p.amount,0)) OVER (
             PARTITION BY tr.project ORDER BY tr.period
             -- ROWS UNBOUNDED PRECEDING -- not strictly necessary here
           ), 
  period = CONVERT(char(6), tr.period, 112)
FROM total_range AS tr
LEFT OUTER JOIN dbo.GLProject AS p 
ON tr.project = p.project
AND tr.period = p.period;

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.