You’re the new DBA for a small sales organization who recently offered customers the opportunity to purchase products online. The sales director has requested to see the number of orders placed online by the month and year before the midmorning traffic meeting. It’s now your responsibility to provide him with this information as soon as possible.
Sales.SalesOrderHeader Table

For our example we will be using just two columns from the Sales.SalesOrderHeader table in the AdventureWorks2012 database. You can download the AdventureWorks2012 database by clicking here.

  • Sales.SalesOrderHeader.OrderDate
  • Sales.SalesOrderHeader.OnlineOrderFlag
Given the above information we know we’ll be using the COUNT function. Let’s put together a simple query which should pull the information.

USE AdventureWorks2012;
SELECT OnlineOrderFlag, OrderDate
FROM Sales.SalesOrderHeader WHERE OnlineOrderFlag = 1;

The basic query above will pull the below results set. However, giving these results to the Sales Director would likely put him in a bad mood and lessen his opinion of our T-SQL skills.

Now, let’s optimize our query a bit to make the Sales Director our new best friend.

USE AdventureWorks2012;
FROM Sales.SalesOrderHeader
WHERE OnlineOrderFlag = 1
GROUP BY DATENAME(mm, OrderDate), DATENAME(yyyy, OrderDate)
ORDER BY Year, Month DESC;

Now that’s more like it!

Mission Accomplished!

Our result set gives us a COUNT of orders and performs a GROUP BY of the year and month. Also, notice that we used the DATENAME function to parse the months name and year.

We can now copy and paste this into Excel and wait for a nice thank you email or a follow up request.

Need to test the height of this box....

Social Wall