Options
Order by Weekday in SQL Widget
Thomas_Hathaway
Customer IT Monkey ✭
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.
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.
0
Best Answer
-
Options
Brian_Wiest
Customer Super IT Monkey ✭✭✭✭✭
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.5
Answers
....
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
....