SqlServer: grouping a date(time) column by month/year

Using AdventureWorks – search elsewhere in my blog for references to that.

The reference to the US locale is that I should be using an explicit conversion – this style is certainly bad practice, but not important for the example. The focus on the 141 value is to show that this was the smallest number of sales in a month/year. The grouping tells me this was for June 2011. So to prove my logic is right, I then select all the rows for June 2011… and they total 141. QED.

-- count sales for each month/year, ordering by 
-- highest sales in a month/year, descending

with salesByMonthYear as (
select 
 DATEPART(month,modifiedDate) salesDateMonth ,
 DATEPART(year,modifiedDate) salesDateYear ,
 count(1) SalesForThisMonthYear
from [AdventureWorks2014].[Sales].[SalesOrderDetail]
group by DATEPART(month,modifiedDate) ,
 DATEPART(year,modifiedDate) 
)
select * from salesByMonthYear
order by SalesForThisMonthYear desc

-- (assuming US locale... bad practice...)
SELECT *
 FROM [AdventureWorks2014].[Sales].[SalesOrderDetail] where 
 ModifiedDate between '6/1/2011' and '06/30/2011'
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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s