February 4, 2010 at 12:02 pm
I am using a dynamic order by in a SSRS Report
(note this is not a working query for example only)
Select No.
Description
Title
Author
Published Date
Mfg Date
from all of my tables
order by Order by Case @orderby when 'Job' then Job.[No_]
when 'Author' then Job.[Description 2]
WHEN 'MSDue' then CASE Job.[MFG Date] WHEN '1753-01-01 00:00:00.000' Then '12-31-2049'
ELSE Job.[MS Due Date]
END
I know I am supposed to use only Text or Dates but when I convert my dates to text they don't sort correctly. Can anyone help me with this conundrum. Thank you
Barb
February 4, 2010 at 12:12 pm
Duplicate post.
Please continue discussion here.
February 4, 2010 at 12:15 pm
Since you cannot cast the entire CASE to a datetime, convert the date to a correctly sortable string, i.e.
CONVERT(varchar(30),cast(CASE Job.[MFG Date] WHEN '1753-01-01 00:00:00.000' Then '12-31-2049'
ELSE Job.[MS Due Date]
END as datetime),121)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 4, 2010 at 12:37 pm
My clause looks like this
when 'SchedDate' then CONVERT(varchar(30),cast(CASE Item.[Scheduled Date] WHEN '1753-01-01 00:00:00.000' Then '12-31-2049'
ELSE Item.[Scheduled Date
END as datetime),121)
I get this message - can we shorten it somehow
Could not generate a list of fields for the query.
Check the query syntax, or click Refresh Fields on the query toolbar.
------------------------------
ADDITIONAL INFORMATION:
The identifier that starts with 'Scheduled Date
END as datetime),121)
' is too long. Maximum length is 128.
Incorrect syntax near ','. (Microsoft SQL Server, Error: 103)
February 4, 2010 at 1:34 pm
I can't see a reason why the following concept wouldn't work...
DECLARE @orderby VARCHAR(30)
SET @orderby='MSDue'
SELECT *
FROM myTable
ORDER BY
CASE @orderby
WHEN 'Job' THEN Col1
WHEN 'Author' THEN Col2
WHEN 'MSDue' THEN
CASE CONVERT(VARCHAR(19),DateCol,120)
WHEN '1753-01-01 00:00:00.000' THEN '12-31-2049'
ELSE CONVERT(VARCHAR(19),DateCol,120)
END
ELSE Col3
END
February 4, 2010 at 1:59 pm
Sorry it is rendering but not sorting at all.
I checked all my Text options work and doing a straight convert the dates sort Alphbetically April, August etc. So I am still at a loss
Any other ideas, thanks
Barb
February 4, 2010 at 2:05 pm
Stop the presses
I was checking character by character there was a space at the begining of my variable name, it wasn't recognizing it. Whooppee it works, I can't thank you enough, I can now finish 5 reports that all depend on this type of sorting.
Thank you , thank you , thank you.
Barb
February 4, 2010 at 4:15 pm
I'm glad I could help! 😀
bboufford (2/4/2010)
...I was checking character by character there was a space at the begining of my variable name, it wasn't recognizing it. ...
Sometimes those rather easy issues will consume most of the time available. Been there, done that. Too often, though... 😉
February 4, 2010 at 5:40 pm
lmu92 (2/4/2010)
I can't see a reason why the following concept wouldn't work...
DECLARE @orderby VARCHAR(30)SET @orderby='MSDue'
SELECT *
FROM myTable
ORDER BY
CASE @orderby
WHEN 'Job' THEN Col1
WHEN 'Author' THEN Col2
WHEN 'MSDue' THEN
CASE CONVERT(VARCHAR(19),DateCol,120)
WHEN '1753-01-01 00:00:00.000' THEN '12-31-2049'
ELSE CONVERT(VARCHAR(19),DateCol,120)
END
ELSE Col3
END
If you hit the default value it won't sort correctly. to sort that correctly, make sure to follow the same pattern. i.e. '2049-12-31'.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 4, 2010 at 5:43 pm
One additional note. If you are dealing with integers instead of date strings, casting/converting integers as varchar will not give the expected ORDER BY results because the number 35 is greater than 4 but the character string '35' is less than '4' .
Instead, try casting the integer as SQL_Variant, which preserves the original characteristics.
;with cte (Name,Col1,Col2) as
(
select 'John','A',100 union all
select 'James','B',120 union all
select 'Jethro',null,4 union all
select 'Jed',null,35 union all
select 'Joey',null,3
)
select * from cte
order by case when COL1 is null then cast(col2 as sql_variant)
else Col1
end
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 5, 2010 at 7:54 am
Thank you all for your input all is well and working great.
February 5, 2010 at 9:26 pm
lmu92 (2/4/2010)
Duplicate post.Please continue discussion here.
I guess you guys kind of missed that one, huh? 😀
--Jeff Moden
February 5, 2010 at 9:58 pm
Nope. I went and looked at it. It got kicked back here.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 6, 2010 at 2:24 am
Seems like I mixed up the links... :blush:
It shows that even here testing is important... 😉
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply