Trouble sorting a Group By in a query

[edmana]

Greetings,

I have a query:

	SELECT Sum(ColumnValue) AS SOCount, ColumnPK
	INTO #TT1010
	FROM 
	(SELECT SUBSTRING(DATENAME(mm, DT.dtime), 0, 4) AS ColumnLabel, Data.ColumnValue, 
	CASE WHEN datepart(mm, DT.dtime) < 10 
	THEN '0' + CAST(datepart(mm, DT.dtime) AS VARCHAR(1)) + '/' +   CAST(datepart(yy, DT.dtime) AS VARCHAR(4)) 
	ELSE  CAST(datepart(mm, DT.dtime) AS VARCHAR(2))  + '/' + CAST(datepart(yy, DT.dtime) AS VARCHAR(4))
	END AS ColumnPK, 'Current period' AS Series
	FROM (

	SELECT COUNT(s.SONumber) AS ColumnValue, DateClosed AS ColumnPK
	FROM tblServiceOrders s
	WHERE s.FKDispatchBoardKeyID = 6 AND s.Status <> 'Void' AND DateOpened Is Not Null AND TimeOpened Is Not Null AND DateClosed Is Not Null and TimeClosed Is Not Null
	GROUP BY s.SONumber, DateClosed

	) AS Data RIGHT OUTER JOIN dbo.tf_udfDateTimes(@CPStartDate, @CPEndDate, 1, 'day', 0) AS DT ON Data.ColumnPK = DT.dtime
	) AS ForGroupBy
	GROUP BY ColumnLabel, ColumnPK, Series

Which gives me the following:

ColumnLabel ColumnValue ColumnPK Series
Jan 73 01/2014 Current period
Feb 89 02/2014 Current period
Mar 73 03/2014 Current period
Apr 58 04/2014 Current period
May 15 05/2014 Current period
Jun 39 06/2013 Current period
Jul 40 07/2013 Current period
Aug 80 08/2013 Current period
Sep 86 09/2013 Current period
Oct 77 10/2013 Current period
Nov 50 11/2013 Current period
Dec 60 12/2013 Current period

I need this to sort by the third column so that the data returned is oldest to newest. I tried to do an ORDER BY on RIGHT(ColumnPK,4) as a start but that did't work. I also tried to do an ORDER BY ColumnLabel to test. That didn't work either.

Can anyone help?

Thanks!
Ed

 

[SkipVought]

Hi,

Why are you changing a Date, which is apparently what you expect to sort by, to a STRING which will sort as NOT expected?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[edmana]

Skip,

I see your point. However, I tried to ORDER BY ColumnLabel which is a 3 letter string showing the month for testing of the ORDER BY after the GROUP BY and it didn't work either.

Ed

 

[SkipVought]

Well do you think that the string Jan will sort before the string Feb???

Actually Apr sorts first, followed by Aug!!!

Pretty sure that's NOT what you expect.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[edmana]

You are correct; that is not the result I ultimately want.

I was using that to see if I could get the data returned to change the sort order from what I was seeing. The returned rows did not change sort order.

I want it sorted by ColumnPK by year and then by month.

Since ColumnPK is returned as a string, not a date (This format is a requirement for something else to which I am interfacing and can't change), I am limited to parsing and converting.

Ideas?

Thanks!
Ed

 

[SkipVought]

Then sort on the substr of columnpk: first the last 4, then the first 2.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[edmana]

Skip,

I can't get the data to sort with an order by after the group by. It doesn't change the sort order regardless of what I try to order by. Hence the reason I posted.

 

[SkipVought]

Please post your SQL.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[edmana]

SELECT Sum(ColumnValue) AS SOCount, ColumnPK
	INTO #TT1010
	FROM 
	(SELECT SUBSTRING(DATENAME(mm, DT.dtime), 0, 4) AS ColumnLabel, Data.ColumnValue, 
	CASE WHEN datepart(mm, DT.dtime) < 10 
	THEN '0' + CAST(datepart(mm, DT.dtime) AS VARCHAR(1)) + '/' +   CAST(datepart(yy, DT.dtime) AS VARCHAR(4)) 
	ELSE  CAST(datepart(mm, DT.dtime) AS VARCHAR(2))  + '/' + CAST(datepart(yy, DT.dtime) AS VARCHAR(4))
	END AS ColumnPK, 'Current period' AS Series
	FROM (

	SELECT COUNT(s.SONumber) AS ColumnValue, DateClosed AS ColumnPK
	FROM tblServiceOrders s
	WHERE s.FKDispatchBoardKeyID = 6 AND s.Status <> 'Void' AND DateOpened Is Not Null AND TimeOpened Is Not Null AND DateClosed Is Not Null and TimeClosed Is Not Null
	GROUP BY s.SONumber, DateClosed

	) AS Data RIGHT OUTER JOIN dbo.tf_udfDateTimes(@CPStartDate, @CPEndDate, 1, 'day', 0) AS DT ON Data.ColumnPK = DT.dtime
	) AS ForGroupBy
	GROUP BY ColumnLabel, ColumnPK, Series 
        ORDER BY CAST(RIGHT(ColumnPK,4) AS INTEGER), CAST(LEFT(ColumnPK,2) AS INTEGER)

 

[SkipVought]

...and please post a sample of the query results.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[edmana]

Actually, I figured it out.

This query was part of a larger stored procedure and the dataset returned came from a different place. I moved the above ORDER BY I created above and all is good.

Sorry for wasting the electrons on this post.
Ed

 

[SkipVought]

Electors cannot be wasted: conservation of energy.

Time, however??????????

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[edmana]

Yes, and time. Sorry for wasting your time Skip.