|
SeanCly10
Starting Member
1 Post |
 Posted - 2009-11-30 : 14:18:57
|
Hi all,I'm currently trying to modify a query to display its output in a certain way, and having no luck. Using the AdventureWorks database, I want to have the query count the number of sales made in 2003 for each month, then display the counts with either the concatenated name (if available), the SalesPersonID number (if the name isn't available), or the word 'AdventureWorks' (if neither the name or ID is there).Here's what I've got so far:quote:
SELECT [SalesPersonID] ,[1] as [Jan] ,[2] as [Feb] ,[3] as [Mar] ,[4] as [Apr] ,[5] as [May] ,[6] as [Jun] ,[7] as [Jul] , as [Aug] ,[9] as [Sep] ,[10] as [Oct] ,[11] as [Nov] ,[12] as [Dec]FROM (SELECT [SalesPersonID],MONTH(OrderDate) as OrderMonth,YEAR(OrderDate) as OrderYear,soh.SalesOrderIDFROM [Person].[Contact] pc INNER JOIN [Sales].[SalesOrderHeader] soh ON pc.[ContactID] = soh.[ContactID])pPIVOT( COUNT(p.SalesOrderID) FOR p.OrderMonth IN ([1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12])) AS pvtWHERE OrderYear = 2003 ORDER BY pvt.SalesPersonID
This makes the correct counts and displays the SalesPersonID number, but I can't think of how to replace it the way I want to and still keep the counts correct. Might anyone have any ideas?-Sean |
|
Query display issues