Problem with the ORDER By Function for my Report

[PatrickCW]

Hey guys,

I have a couple of Reports in Use that Document the Time worked for a Custom Field "Category" that's just a Basic Combobox to categorize/label the Tickets for example as "VPN, Office365, Lansweeper, User, Client, Hardware etc."

With this I already have reports that count the Time correctly per Week, month and year but all of them share the common issue that the Order By Function doesn't really seem to work. When I try to order it by the "TimeWorked" it won't work because the Reports are using the "SELECT DISTINCT" Function and not just a regular SELECT.

I had found out a way to order them by just regular time but then it seems to just order by the first Number and then by the Second for the "Days" for example - "9 days 5 hours 30 minutes" is above the "32 days 15 hours 45 minutes" one because it doesn't seem to Order By Minutes worked, instead it orders by days (?)

Here is my current Report, would appreciate any help! 🙂

Select Distinct Top 1000000 htblticketcustomfield.data As Category,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) /
60 / 24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) /
60 % 24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) %
60))) + ' minutes' As TimeWorked
From htblticketcustomfield
Inner Join htblcustomfields On htblticketcustomfield.fieldid =
htblcustomfields.fieldid
Inner Join (Select Top 1000000 htblticketcustomfield.data,
Sum(htblnotes.timeworked) As MinutesWorked
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Inner Join htblnotes On
htblnotes.ticketid = htblticketcustomfield.ticketid
Inner Join htblhistory On htblticketcustomfield.ticketid =
htblhistory.ticketid
Inner Join htblticketstates On htblticketstates.ticketstateid =
htblhistory.ticketstateid
Inner Join htblticket On
htblticket.ticketid = htblticketcustomfield.ticketid
Inner Join htblhistorytypes On htblhistorytypes.typeid =
htblhistory.typeid
Where htblticketstates.statename = 'Closed' And htblticketstates.statename =
'Closed' And htblticket.spam <> 'True' And htblhistorytypes.name
In ('Status changed', 'Note added and state changed',
'Internal note added and state changed') And
htblcustomfields.name Like '%Category%' And DatePart(isoww,
htblnotes.date) = DatePart(isoww, GetDate()) And DatePart(yyyy,
htblnotes.date) = DatePart(yyyy, GetDate()) And
htblnotes.timeworked Is Not Null
Group By htblticketcustomfield.data
Having Max(htblhistory.date) > GetDate() - 365) As Custom1 On
Custom1.data = htblticketcustomfield.data
Order by TimeWorked Desc

[PatrickCW]

Hi there,

how exactly would I add a 0 beforehand to the "days"?

The Order By is working fine for other reports where the TimeWorked is being reported, just not for the ones where im counting based of the time input for the categories alone...
There I can Order By "Order By WorkTime.MinutesWorked Desc,
Agent"
and that works perfectly but I can't Order By that in the Category one since it's a Select Distinct

[KevinA-REJIS]

I wasn't able to test this so it might not work, but try using this code for the Days portion:

Right('00' + Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) /
60 / 24))), 2) + ' days'

That should add two zeros to the front of the days number, then select the last two digits. 

[PatrickCW]

Hi Kevin,

thank you, that worked out perfectly - I had to make a minor Adjustments though, I had to add the "+" at the End of it and I've added the added in 0's for hours and minutes also so the whole Portion looks like this now

Right('00' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) /
60 / 24))), 2) + ' days ' + Right('00' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) /
60 % 24))), 2) + ' hours ' + Right('00' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) %
60))), 2) + ' minutes' As TimeWorked

And that is working perfectly how I want it to, thank you! 🙂

[KevinA-REJIS]

Looks like it is sorting by the first digit of the string, so, for example, it sees 9 as being greater than 18. If you can add a zero to the single digit days (i.e. 09), it should sort the way you intended.

[PatrickCW]

This is a Screenshot of the Order By Error I have when doing this current one