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!