Aggregate Monthly Data and Sort by Month


  

 create table test (id int identity(1,1) primary key, dt datetime,amount decimal(10,2))
insert into  test (dt,amount) values
(getdate(),1.05),
(dateadd(month,-3,getdate()),1.05),
(getdate(),1.05),
(dateadd(month,-3,getdate()),1.05),
(dateadd(month,-2,getdate()),1.05),
(dateadd(month,-3,getdate()),1.05),
(dateadd(month,0,getdate()),1.05),
(dateadd(month,1,getdate()),1.05),
(dateadd(month,2,getdate()),1.05),
(dateadd(month,3,getdate()),1.05),
(dateadd(month,4,getdate()),1.05),
(dateadd(month,5,getdate()),1.05),
(dateadd(month,6,getdate()),1.05),
(dateadd(month,7,getdate()),1.05),
(dateadd(month,8,getdate()),1.05),
(dateadd(month,3,getdate()),1.05),
(dateadd(month,4,getdate()),1.05),
(dateadd(month,9,getdate()),1.05) 

select DATENAME(mm,dt) mnth,
        Amount  = SUM(Amount) 
 from test
 WHERE 
 dt>=dateadd(year,datediff(year,0,getdate()),0) and
 dt<dateadd(year,datediff(year,0,getdate())+1,0)
Group by DATENAME(mm,dt) 
ORDER BY CAST(DATENAME(mm,dt) + ' 1900' AS DATETIME)

drop table   test

 

https://www.sqlservercentral.com/articles/sorting-months-by-number-sql-spackle

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s