asked on

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.

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

Mike Eghtebas

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 Eghtebas

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 Horn

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

Southern_Gentleman

ASKER

I seem to be getting a Conversion failed when converting date and/or time from character string.

ASKER CERTIFIED SOLUTION

Mike Eghtebas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Southern_Gentleman

ASKER

Thanks a bunch

Mike Eghtebas

Did you use cte solution?