November 30, 2009 at 8:58 am
Hi all,
I'm working with the AdventureWorks database, trying to learn T-SQL better and hopefully expand my knowledge some. I want to write a query that will look at the OrderDate column of the Sales.SalesOrderHeader table and count the number of orders per month for a single year, but parse them out as columns. For example, this would be the output I'm trying to achieve:
Employee Name Jan Feb Mar
--------------- --- --- ---
Bob 3 4 3
Joe 7 3 10
...and so forth. Could someone give me an idea of how to accomplish this? I feel like I'm missing something painfully obvious.
Thanks all,
Sean
November 30, 2009 at 9:56 am
There may be an easier way, but the only way I know of to do this is to use a PIVOT statement.
select FirstName, [1] as Jan, [2] as Feb, [3] as Mar
from
(
select
c.FirstName
,datepart(mm,OrderDate) as OrderMonth
,so.SalesOrderID
from
Sales.SalesOrderHeader so
join Person.Contact c on so.ContactID = c.ContactID
) as o
pivot
(
count(o.SalesOrderID)
for o.OrderMonth in ([1], [2], [3])
) as pv
order by pv.FirstName
This only does the first 3 months of the year. In order to get all the months you'd have to add them to the top "select" and the "for" lines.
I hope this helps.
November 30, 2009 at 10:40 am
That's wonderful. It works exactly as I needed. I figured that PIVOT would be where I needed to go, but I had no idea how to format it. Thank you.
There's something else that I'm running into, though...the numbers that I'm getting are not the ones that I'm supposed to be getting, and I suspect that it's because the query is counting by the employee name instead of the SalesPersonID. For example, counting by the SalesPersonID = 278 gets me 11 orders for the month of February, but counting on the name Carla L. Eldridge, which is number 278 and is supposed to be the same thing, nets me only 1.
Here's my query as it stands:
SELECT
[Employee Name]
,[1] as [Jan]
,[2] as [Feb]
,[3] as [Mar]
,[4] as [Apr]
,[5] as [May]
,[6] as [Jun]
,[7] as [Jul]
,[8] as [Aug]
,[9] as [Sep]
,[10] as [Oct]
,[11] as [Nov]
,[12] as [Dec]
FROM
(SELECT
pc.[FirstName] + ' ' + COALESCE(pc.[MiddleName], '') + ' ' + pc.[LastName] AS [Employee Name]
,MONTH(OrderDate) as OrderMonth
,YEAR(OrderDate) as OrderYear
,soh.SalesOrderID
FROM [Person].[Contact] pc
INNER JOIN [Sales].[SalesOrderHeader] soh
ON pc.[ContactID] = soh.[ContactID])p
PIVOT
(
COUNT(p.SalesOrderID)
FOR p.OrderMonth
IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))
AS pvt
WHERE OrderYear = 2003
ORDER BY pvt.[Employee Name]
I suspect that this results from NULLs in certain places, but I don't know how to account for them. The problem is that I want to display the names instead of the SalesPersonID numbers. Can you help?
December 1, 2009 at 9:55 am
SalesPersonID and ContactID are 2 different fields in the SalesOrderHeader table. The SalesPersonID is not 278 when the ContactID = 'Carla L. Eldridge'. That's why you are getting different results. You are also limiting the data to OrderYear 2003. Here are 3 different queries to show the difference
SELECT [EmployeeName] ,sum([1]) as [Jan],sum([2]) as [Feb],sum([3]) as [Mar],sum([4]) as [Apr]
,sum([5]) as [May],sum([6]) as [Jun],sum([7]) as [Jul],sum([8]) as [Aug],sum([9]) as [Sep]
,sum([10]) as [Oct],sum([11]) as [Nov],sum([12]) as [Dec]
FROM (SELECT
pc.[FirstName] + ' ' + COALESCE(pc.[MiddleName], '') + ' ' + pc.[LastName] AS [EmployeeName],SalesPersonID
,MONTH(OrderDate) as OrderMonth,YEAR(OrderDate) as OrderYear,soh.SalesOrderID
FROM [Person].[Contact] pc
INNER JOIN [Sales].[SalesOrderHeader] soh
ON pc.[ContactID] = soh.[ContactID])p
PIVOT
(
COUNT(p.SalesOrderID)
FOR p.OrderMonth
IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))
AS pvt
WHERE EmployeeName = 'Carla L. Eldridge'
group BY pvt.[EmployeeName]
ORDER BY pvt.[EmployeeName]
SELECT [SalesPersonID] ,sum([1]) as [Jan],sum([2]) as [Feb],sum([3]) as [Mar],sum([4]) as [Apr]
,sum([5]) as [May],sum([6]) as [Jun],sum([7]) as [Jul],sum([8]) as [Aug],sum([9]) as [Sep]
,sum([10]) as [Oct],sum([11]) as [Nov],sum([12]) as [Dec]
FROM (SELECT
pc.[FirstName] + ' ' + COALESCE(pc.[MiddleName], '') + ' ' + pc.[LastName] AS [EmployeeName],SalesPersonID
,MONTH(OrderDate) as OrderMonth,YEAR(OrderDate) as OrderYear,soh.SalesOrderID
FROM [Person].[Contact] pc
INNER JOIN [Sales].[SalesOrderHeader] soh
ON pc.[ContactID] = soh.[ContactID])p
PIVOT
(
COUNT(p.SalesOrderID)
FOR p.OrderMonth
IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))
AS pvt
WHERE EmployeeName = 'Carla L. Eldridge' group BY pvt.[SalesPersonID]
ORDER BY pvt.[SalesPersonID]
SELECT [SalesPersonID] ,sum([1]) as [Jan],sum([2]) as [Feb],sum([3]) as [Mar],sum([4]) as [Apr]
,sum([5]) as [May],sum([6]) as [Jun],sum([7]) as [Jul],sum([8]) as [Aug],sum([9]) as [Sep]
,sum([10]) as [Oct],sum([11]) as [Nov],sum([12]) as [Dec]
FROM (SELECT
pc.[FirstName] + ' ' + COALESCE(pc.[MiddleName], '') + ' ' + pc.[LastName] AS [EmployeeName],SalesPersonID
,MONTH(OrderDate) as OrderMonth,YEAR(OrderDate) as OrderYear,soh.SalesOrderID
FROM [Person].[Contact] pc
INNER JOIN [Sales].[SalesOrderHeader] soh
ON pc.[ContactID] = soh.[ContactID])p
PIVOT
(
COUNT(p.SalesOrderID)
FOR p.OrderMonth
IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))
AS pvt
WHERE SalesPersonID = 278
group BY pvt.[SalesPersonID]
ORDER BY pvt.[SalesPersonID]
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 1, 2009 at 12:34 pm
Consider the faster/sometimes easier alternative to Pivot...
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply