Service Manager Portal
Thomas_Hathaway
Thomas_Hathaway
·Posted in Service Manager Portal

Order by Weekday in SQL Widget

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. 
2 replies