Showing results for 
Show  only  | Search instead for 
Did you mean: 
Engaged Sweeper II

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 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 =
Inner Join (Select Top 1000000,
Sum(htblnotes.timeworked) As MinutesWorked
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
Inner Join htblnotes On
htblnotes.ticketid = htblticketcustomfield.ticketid
Inner Join htblhistory On htblticketcustomfield.ticketid =
Inner Join htblticketstates On htblticketstates.ticketstateid =
Inner Join htblticket On
htblticket.ticketid = htblticketcustomfield.ticketid
Inner Join htblhistorytypes On htblhistorytypes.typeid =
Where htblticketstates.statename = 'Closed' And htblticketstates.statename =
'Closed' And htblticket.spam <> 'True' And
In ('Status changed', 'Note added and state changed',
'Internal note added and state changed') And Like '%Category%' And DatePart(isoww, = DatePart(isoww, GetDate()) And DatePart(yyyy, = DatePart(yyyy, GetDate()) And
htblnotes.timeworked Is Not Null
Group By
Having Max( > GetDate() - 365) As Custom1 On =
Order by TimeWorked Desc

Engaged Sweeper II

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,
and that works perfectly but I can't Order By that in the Category one since it's a Select Distinct

Champion Sweeper III

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. 

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! 🙂



Champion Sweeper III

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.

Engaged Sweeper II

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