SQLTeam.com | Weblogs | Forums

Order by TimeWorked correctly

Hey guys,

I have a problem with this Code, currently it is ORDER by the Category Name (A-Z)
But I want it to sort with the most TimeWorked at the top (36days above 4days for example)

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%'
    Group By htblticketcustomfield.data
    Having Max(htblhistory.date) > GetDate() - 120) As Custom1 On
      Custom1.data = htblticketcustomfield.data

Any help or suggestions would be greatly appreciated! :slight_smile:

Kind regards,
Patrick

can't really tell by the query with no data, but try

order by 2 desc

Hi there,

I've tried this method already but now it puts the "9" up to the Top and counting there from the first number and then goes on the second number so "36" is for example way below the 9 even though it is more.

Try order by Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) / 60 / 24)))

Hi there,

this error came out of it when trying to add it:

image

This error is very clear - if you are using DISTINCT in the query then the order by item has to exist in the select statement. The convert statement that @mike01 provided is not the full statement for that column.

The actual problem you have is that you are trying to sort by a string value. It is sorting correctly based on string sorting - but you want it to sort numerically by the 'days'. And I am going to assume you would want it to sort correctly based on the hours and minutes also.

You could try using a CTE/derived table - then in the outer query parse the TimeWorked in the ORDER BY to just get the days. That might work, but there is actually an easier method.

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

Change to:

concat(right('0', convert(nchar(2), Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) / 60 / 24))), 2), ' days ',  right(Convert(nchar(2),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) /
  60 % 24))), 2), ' hours ', right(convert(nchar(2),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) %
  60)))), ' minutes') As TimeWorked

Note: not tested - so there may be missing/incorrect parentheses.

This forces each entry to be patterned as DD days HH hours MM minutes. For example: 09 days 02 hours 10 minutes - which will sort below 36 days 14 hours 40 minutes in descending order based on string sorting rules.

1 Like

Hi Jeff,

now it presents me with this error:
image

This is my current Code:

Select Distinct Top 1000000 htblticketcustomfield.data As Category,
  Concat(Right('0',
  Convert(nchar(2),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) / 60 /
  24))), 2), ' days ',
  Right(Convert(nchar(2),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) /
  60 % 24))), 2), ' hours ',
  Right(Convert(nchar(2),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

I haven't even gotten to the Order By Part yet :confused:

Is this for Microsoft SQL Server?

Well - I did state this was not tested code and that there could be issues with parentheses. I would expect that you could identify the missing/incorrect parts and get them fixed.

concat(
                    Right('0', Convert(nchar(2), Ceiling(Floor(Convert(integer, Custom1.MinutesWorked) / 60 / 24))), 2)
                  , ' days '
                  , Right(Convert(nchar(2), Ceiling(Floor(Convert(integer, Custom1.MinutesWorked) / 60 % 24))), 2)
                  , ' hours '
                  , Right(Convert(nchar(2), Ceiling(Floor(Convert(integer, Custom1.MinutesWorked) % 60))), 2)
                  , ' minutes')

Looks like I missed the '2' for the minutes.

Once you get that done - then just

Order By TimeWorked desc

Hi,

no unfortunately not - it's basic SQL as far as I can tell.
Is this Topic categorized wrong?

ok, SQL is "a language used in programming and designed for managing data held in a relational database management system, or for stream processing in a relational data stream management system"

My question was is your database product: mysql, postgres, foxpro :slight_smile: or sql server from microsoft?

I posted an update - have you tried that yet?

Hi there,

yes sorry - I have but unfortunately the same error occured as last time
image

I am actually using the "Report Builder" via "Lansweeper" it has a built-in Report-Builder that creates Reports based of the Database Data and is working with the SQL Language.

:smile: so the databaae data you are querying is it Microsoft sql server?

Yes, the data is from a Microsoft SQL Server

I see the problem with the original code - and my changes. So going back to the original issue - this is a sorting problem because of the way string sorting works. The idea is to format the string in such a way that it sorts correctly.

concat(right('0' + convert(nchar(2), Custom1.MinutesWorked / 60 / 24), 2) + ' days ' 
     + right('0' + convert(nchar(2), Custom1.MinutesWorked / 60 % 24, 2) + ' hours '
     + right('0' + convert(nchar(2), Custom1.MinutesWorked % 60, 2) + ' minutes'

Simplifying the statement so we can see each part - what we need to do is calculate the days, hours and minutes with leading zeroes. The leading zeroes forces the sort order to work as expected.

Removing the ceiling/floor calculations because they are not needed due to integer math.