0

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!

1 Answer 1

3

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;

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).

1
  • Great! Thanks again @Aaron Bertrand! Also thanks for describing the issue as this helps my understanding of writing sql queries. I had to add back Sum(Sum()) and Group BY at the end, because otherswise I got duplicate periods if there were more than one transaction in that period.
    – Clueless
    Commented Sep 15, 2021 at 7:23

Your Answer

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

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