Aggregate PARTITION BY 3 columns, one being year. Adds multiple projects in every year after first

Question

I have gotten help from the community to build a query that gives me accumulated amount by project and company for each period until today. It also makes sure that if there are no records in a period it will leave 0 instead of skipping that period. Now I'm trying to add partition by year into this in order to get year-to-date numbers. It seems to work fine for the first year, however second year it adds (in my sample) both projects togehter.

Fiddle for this example is here: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=d00caa265d67f4702a31c1f011ebe058

This is my code:

;WITH total_range AS 
(
  SELECT Comp, project, datecol = MIN(datecol), Yr
    FROM GLProject
    GROUP BY Comp, project, Yr
  UNION ALL 
  SELECT Comp, project, DATEADD(MONTH, 1, datecol), Yr = YEAR(DATEADD(MONTH, 1, datecol))
  FROM total_range 
  WHERE datecol < @d
  )
SELECT 
  tr.project, tr.Comp, 
  amount = SUM(SUM(coalesce(p.amount, 0))) 
  OVER (
             PARTITION BY tr.comp, tr.project, tr.Yr ORDER BY tr.datecol
 ROWS UNBOUNDED PRECEDING
           ), 
  period = CONVERT(char(6), tr.datecol, 112)
FROM total_range AS tr
LEFT OUTER JOIN GLProject AS p 
ON concat(tr.Comp, tr.project) = concat(p.Comp, p.project)
AND tr.datecol = p.datecol
group by tr.Comp, tr.project, tr.datecol, tr.Yr

Output for 202101 (January) is accumulated sum (10+10) of both projects. I would like it to be just Year-to-date for each project individually.

Example output

project	Comp	amount	period	Yr
12345	AA	20	202101	2021
12345	AA	40	202102	2021
99999	AA	20	202101	2021
99999	AA	40	202102	2021

What I would like my example output to be is:

project	Comp	amount	period	Yr
12345	AA	10	202101	2021
12345	AA	20	202102	2021
99999	AA	10	202101	2021
99999	AA	20	202102	2021

Hope it's just a small thing I'm missing.

Thanks for alle the great help!

Answer 1

The problem is that total_range includes duplicates before you've grouped or otherwise filtered them out, so their amounts are counting double. By making all of the amounts 10 (and then grouping later) you've kind of hidden this from yourself. This is more apparent if you just mess with a couple of numbers, like this fiddle, or by just adding row numbers to the output of the very first CTE, like this fiddle.

One way to remove the duplicates that come out of the recursive UNION ALL is to simply have a second CTE that groups first (you could also add a row number there and filter on rn = 1 next).

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

;WITH raw_range AS 
(
  SELECT Comp, project, datecol = MIN(datecol)
    FROM GLProject
    GROUP BY Comp, project, DATEPART(YEAR, datecol)
  UNION ALL
  SELECT Comp, project, DATEADD(MONTH, 1, datecol)
  FROM raw_range 
  WHERE datecol < @d
),
total_range AS 
(
  SELECT Comp, project, datecol,  
         Yr = DATEPART(YEAR, datecol)
  FROM raw_range
  GROUP BY Comp, project, datecol
)
SELECT tr.project, 
       tr.Comp, 
       amount = SUM(COALESCE(p.amount,0)) OVER 
                (PARTITION BY tr.Comp, tr.Project, tr.Yr 
                 ORDER BY tr.datecol),
       period = tr.datecol
FROM total_range AS tr
LEFT JOIN dbo.GLProject AS p
  ON tr.project = p.project
 AND tr.Comp    = p.Comp
 AND tr.datecol = p.datecol
ORDER BY tr.Comp, tr.project, tr.datecol;

• Example db<>fiddle

Note you still don't need ROWS UNBOUNDED PRECEDING, you still don't need nested SUM(SUM()), and you still don't need GROUP BY on the final output. And I'm not sure why you need a separate Yr column on the source table, since you can always find the year from a date, so the information is redundant. I'd also stay away from relying on CONCAT() to, uh, "simplify" joins, since CONCAT('A', null, 'BC') === CONCAT('AB', 'C', null).