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:
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.
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!