Order by Weekday in SQL Widget

Thomas_Hathaway

Within our environment we have team dashboards that groups work items by weekday (datename(weekday,wi.Created) as 'Day') but its setting the order by alphabetically and not by date. So i created a, order by case scenario which works within management studio but not the dashboard. Here is the query in its entirety, any suggestions to a workaround? 

select 

wi.AssignedUser as 'Analyst', 

wi.WorkItemId as 'Ticket',

datename(weekday,wi.Created) as 'Day', 

DS.DisplayString as 'Support Group'

FROM WorkItem wi join DisplayString ds

on wi.tierid = ds.elementid

where

 

wi.TierId in  

('431834D2-6EEC-AC86-50FE-562200B39CDE', 

'FB73E6FF-704E-9849-9782-684362FB75B0', 

'5FF929EF-5DB1-3319-6F4C-9DFEB2CBA87B',

'BD3603CD-EDC0-A775-0BF9-1C6A5A185E9F',  

'35DD3589-C3FD-F03E-7ECD-79DBC0DB4934',

'E49AB112-3D6F-8886-3C55-49BF13DDBCE4',

'A61482EE-1316-2FF1-0D0B-6C659A9C10D4',

'1EDB2155-339E-3C45-7EFA-C02D2B0FA722') 

and

wi.Created >= DATEADD(day,-7,GETDATE())

order by

   case datename(weekday, wi.created)

      when 'Monday' then 1

      when 'Tuesday' then 2

      when 'Wednesday' then 3

      when 'Thursday' then 4

      when 'Friday' then 5

      when 'Saturday' then 6

      when 'Sunday' then 7

end

results in studio are as i hoped it would be m-s in correct order but within the dashboard im still getting an alphabetical result. 

Brian_Wiest

Tried a few variants as getting the same behavior. Could be some way that its picking the column name. I tested on v8.0, wonder if they added column ordering to their logic. 

Brian_Wiest

Tried a few variants as getting the same behavior. Could be some way that its picking the column name. I tested on v8.0, wonder if they added column ordering to their logic. 

Thomas_Hathaway

The workaround I came up with instead moved the case clause into the select statement instead, looks like this:
....

    datename(weekday,wi.Created) as 'Day',

    DS.DisplayString as 'Support Group' ,

       case datename(weekday, wi.created)

          when 'Monday' then '1 Monday'

          when 'Tuesday' then '2 Tuesday'

          when 'Wednesday' then '3 Wednesday'

          when 'Thursday' then '4 Thursday'

          when 'Friday' then '5 Friday'

          when 'Saturday' then '6 Saturday'

          when 'Sunday' then '7 Sunday'

      end  as 'DayNbr'

 

FROM WorkItem wi join DisplayString ds

  on wi.tierid = ds.elementid

....