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!
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.
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 ' +
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.
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
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.
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 or sql server from microsoft?
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.
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.
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.
so the databaae data you are querying is it Microsoft sql server?