Forum Discussion
Sql to calculate quarterly/annual aggregation aside of monthly numbers
Hi,
I am struggling to calculate amounts based on mtd amounts w/o using a cursor. Any idea?
create table #raw(quarter int, name varchar(10), year int, month int, amount decimal(19,2))
insert #raw(quarter, name, year, month, amount)
values(1, 'aa', 2025,1,2.0),(1, 'bb', 2025,1,4.0),(1, 'cc', 2025,1,1.0),
(1, 'aa', 2025,2,6.0),(1, 'bb', 2025,2,8.0),
(1, 'aa', 2025,3,10.0),(1, 'bb', 2025,3,2.0),(1, 'dd', 2025,3,4.0),
(2, 'ee', 2025,4,9.0),(2, 'bb', 2025,4,3.0),(2, 'cc', 2025,4,3.0),(2, 'aa', 2025,4,1.0),
(2, 'ee', 2025,5,15.0),(2, 'bb', 2025,5,1.0),(2, 'cc', 2025,5,2.0),(2, 'aa', 2025,5,7.0),
(2, 'cc', 2025,6,8.0),(2, 'aa', 2025,6,9.0)
Annual calc is easy, but not sure how to add quarterly number aside:
select mtd.quarter, mtd.name, mtd.year, mtd.month, mtd.amount, sum(ytd.amount) as ytd
from #raw mtd
join #raw ytd on mtd.name = ytd.name and mtd.year = ytd.year and mtd.month >= ytd.month and mtd.quarter >= ytd.quarter
group by mtd.quarter, mtd.name, mtd.year, mtd.month, mtd.amount
order by 1,3,4,2
Goal is to get a report like this:
Actually, with temp tables:
select mtd.quarter, mtd.name, mtd.year, mtd.month, mtd.amount, sum(ytd.amount) as ytd
into #ytd
from #raw mtd
join #raw ytd on mtd.name = ytd.name and mtd.year = ytd.year and mtd.month >= ytd.month and mtd.quarter >= ytd.quarter
group by mtd.quarter, mtd.name, mtd.year, mtd.month, mtd.amount
order by 1,3,4,2
select mtd.quarter, mtd.name, mtd.year, mtd.month, mtd.amount, sum(qtd.amount) as qtd
into #qtd
from #raw mtd
join #raw qtd on mtd.name = qtd.name and mtd.year = qtd.year and mtd.month >= qtd.month and mtd.quarter = qtd.quarter
group by mtd.quarter, mtd.name, mtd.year, mtd.month, mtd.amount
order by 1,3,4,2
select ytd.quarter, ytd.name, ytd.year, ytd.month, ytd.amount, qtd.qtd, ytd.ytd
from #ytd ytd
join #qtd qtd on qtd.name = ytd.name and qtd.year = ytd.year and qtd.month = ytd.month and qtd.quarter = ytd.quarter
order by 1,3,4,2
2 Replies
Actually, with temp tables:
select mtd.quarter, mtd.name, mtd.year, mtd.month, mtd.amount, sum(ytd.amount) as ytd
into #ytd
from #raw mtd
join #raw ytd on mtd.name = ytd.name and mtd.year = ytd.year and mtd.month >= ytd.month and mtd.quarter >= ytd.quarter
group by mtd.quarter, mtd.name, mtd.year, mtd.month, mtd.amount
order by 1,3,4,2
select mtd.quarter, mtd.name, mtd.year, mtd.month, mtd.amount, sum(qtd.amount) as qtd
into #qtd
from #raw mtd
join #raw qtd on mtd.name = qtd.name and mtd.year = qtd.year and mtd.month >= qtd.month and mtd.quarter = qtd.quarter
group by mtd.quarter, mtd.name, mtd.year, mtd.month, mtd.amount
order by 1,3,4,2
select ytd.quarter, ytd.name, ytd.year, ytd.month, ytd.amount, qtd.qtd, ytd.ytd
from #ytd ytd
join #qtd qtd on qtd.name = ytd.name and qtd.year = ytd.year and qtd.month = ytd.month and qtd.quarter = ytd.quarter
order by 1,3,4,2
Actually, it is easy with temp tables:
select mtd.quarter, mtd.name, mtd.year, mtd.month, mtd.amount, sum(ytd.amount) as ytd
into #ytd
from #raw mtd
join #raw ytd on mtd.name = ytd.name and mtd.year = ytd.year and mtd.month >= ytd.month and mtd.quarter >= ytd.quarter
group by mtd.quarter, mtd.name, mtd.year, mtd.month, mtd.amount
order by 1,3,4,2
select mtd.quarter, mtd.name, mtd.year, mtd.month, mtd.amount, sum(qtd.amount) as qtd
into #qtd
from #raw mtd
join #raw qtd on mtd.name = qtd.name and mtd.year = qtd.year and mtd.month >= qtd.month and mtd.quarter = qtd.quarter
group by mtd.quarter, mtd.name, mtd.year, mtd.month, mtd.amount
order by 1,3,4,2
select ytd.quarter, ytd.name, ytd.year, ytd.month, ytd.amount, qtd.qtd, ytd.ytd
from #ytd ytd
join #qtd qtd on qtd.name = ytd.name and qtd.year = ytd.year and qtd.month = ytd.month and qtd.quarter = ytd.quarter
order by 1,3,4,2