Order By

This may be a little confusing to explain but i'm looking for an Order By solution where my dates are ascending based upon non-existing NULL values. So my first column is Ascending from the top down where there are no Null Values. The issue is that there may be dates in my other columns in the same row. I would like to keep all the dates that are the results of the selected row. I am able to Order By my column named [1] . Then I want the same results in column named [2] in the row below the final result of [1]. Then keep the same pattern until column [10].

Here is my result that come up and the results that i'm looking for.

Results
Here is my current pivot select query:

SELECT  *   
FROM         (SELECT     ec.CategoryID, CRM.EventName, CRM.City, CRM.State, CRM.ContactName, CRM.ContactNumber, CRM.Email, CRM.EventDate                      
FROM         CRMEventDetail AS CRM LEFT OUTER JOIN
                      aspnet_Users AS u ON CRM.AccountManager = u.UserId LEFT OUTER JOIN
                      EventCategories AS ec ON CRM.EventCategory = ec.EventCategory
WHERE     (CRM.EventDate >= { fn NOW() }) AND (u.FullName = @FullName)
GROUP BY CRM.EventId, CRM.EventName, CRM.City, CRM.State, CRM.ContactName, CRM.ContactNumber, CRM.Email,
                      CRM.AccountManager, ec.CategoryID, CRM.EventDate		 
                       ) AS derivedtbl_1
PIVOT (
MAX(EventDate) FOR CategoryID IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) AS pvt
ORder By
	CASE WHen [1] is Null Then 1 ELSE 0 END,
	[1] asc

Open in new window

Southern_GentlemanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
SELECT  *   
FROM         (SELECT     ec.CategoryID, CRM.EventName, CRM.City, CRM.State, CRM.ContactName, CRM.ContactNumber, CRM.Email, CRM.EventDate                      
FROM         CRMEventDetail AS CRM LEFT OUTER JOIN
                      aspnet_Users AS u ON CRM.AccountManager = u.UserId LEFT OUTER JOIN
                      EventCategories AS ec ON CRM.EventCategory = ec.EventCategory
WHERE     (CRM.EventDate >= { fn NOW() }) AND (u.FullName = @FullName)
GROUP BY CRM.EventId, CRM.EventName, CRM.City, CRM.State, CRM.ContactName, CRM.ContactNumber, CRM.Email,
                      CRM.AccountManager, ec.CategoryID, CRM.EventDate		 
                       ) AS derivedtbl_1
PIVOT (
MAX(EventDate) FOR CategoryID IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) AS pvt
ORder By
	COALESCE ([1],'z')
     ,  COALESCE ([2],'z')
     ,  COALESCE ([3],'z')
     ,  COALESCE ([4],'z')

Open in new window

Mike EghtebasDatabase and Application DeveloperCommented:
maybe we need to use a cte:

;with cte As
(
SELECT  *  
FROM         (SELECT     ec.CategoryID, CRM.EventName, CRM.City, CRM.State, CRM.ContactName, CRM.ContactNumber, CRM.Email, CRM.EventDate                      
FROM         CRMEventDetail AS CRM LEFT OUTER JOIN
                      aspnet_Users AS u ON CRM.AccountManager = u.UserId LEFT OUTER JOIN
                      EventCategories AS ec ON CRM.EventCategory = ec.EventCategory
WHERE     (CRM.EventDate >= { fn NOW() }) AND (u.FullName = @FullName)
GROUP BY CRM.EventId, CRM.EventName, CRM.City, CRM.State, CRM.ContactName, CRM.ContactNumber, CRM.Email,
                      CRM.AccountManager, ec.CategoryID, CRM.EventDate            
                       ) AS derivedtbl_1
PIVOT (
MAX(EventDate) FOR CategoryID IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) AS pvt
)
Select * From cte
Order By
      COALESCE ([1],'z')
     ,  COALESCE ([2],'z')
     ,  COALESCE ([3],'z')
     ,  COALESCE ([4],'z')
Jim HornMicrosoft SQL Server Data DudeCommented:
Guessing there's a more elegant way to pull this off, but the below code executed correctly in my SQL 2012 box
CREATE TABLE #tmp (Event1 datetime NULL, Event2 datetime NULL, Event3 datetime NULL, Event4 datetime NULL)

INSERT INTO #tmp (Event1, Event2, Event3, Event4 )
VALUES 
   ('20151017', NULL, NULL, NULL), 
   ('20151017', '20151024', NULL, NULL), 
   ('20151017', NULL, NULL, NULL), 
   ('20151017', NULL, NULL, NULL), 
   (NULL, '20151017', NULL, NULL), 
   (NULL, '20151024', NULL, NULL),
   (NULL, NULL, '20151001', NULL), 
   (NULL, NULL, '20141001', NULL), 
   (NULL, NULL, NULL, '20151230'),
   (NULL, NULL, NULL, '20151231')

SELECT Event1, Event2, Event3, Event4
FROM #tmp
ORDER BY 
   CASE WHEN Event1 IS NULL THEN 1 ELSE 0 END, Event1, 
   CASE WHEN Event2 IS NULL THEN 1 ELSE 0 END, Event2, 
   CASE WHEN Event3 IS NULL THEN 1 ELSE 0 END, Event3, 
   CASE WHEN Event4 IS NULL THEN 1 ELSE 0 END, Event4

Open in new window

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Southern_GentlemanAuthor Commented:
I seem to be getting a Conversion failed when converting date and/or time from character string.
Mike EghtebasDatabase and Application DeveloperCommented:
try:

      COALESCE ([1],'2500-01-01')
     ,  COALESCE ([2],'2500-01-01')
     ,  COALESCE ([3],'2500-01-01')
     ,  COALESCE ([4],'2500-01-01')

if you are deferring to my posts.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Southern_GentlemanAuthor Commented:
Thanks a bunch
Mike EghtebasDatabase and Application DeveloperCommented:
Did you use cte solution?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.