Helpdesk report, average time ticket response, need times in hours not days

[Tholmes]

I found this report here
https://www.lansweeper.com/report/average-time-until-initial-ticket-response-by-month/
I worked out that 864000 is seconds in a day, but then, I can't work out how to change that into hours and mins.
Any help please, would be greatly appreciated.


Select Top 1000000 DatePart(yyyy, htblticket.date) As Year,
DatePart(mm, htblticket.date) As Month,
Convert(Decimal(10,4),Avg(Cast(DateDiff(ss, htblticket.date,
InitialResponseDate.FirstPublicReply) As decimal) / 86400)) As AverageDays
From htblticket
Inner Join (Select Top 1000000 htblnotes.ticketid,
Min(htblnotes.date) As FirstPublicReply
From htblnotes
Inner Join htblagents On htblagents.userid = htblnotes.userid
Where htblnotes.notetype = 1
Group By htblnotes.ticketid) As InitialResponseDate On
InitialResponseDate.ticketid = htblticket.ticketid
Where htblticket.spam <> 'True'
Group By DatePart(yyyy, htblticket.date),
DatePart(mm, htblticket.date)
Order By Year Desc,
Month Desc

Thanks in advance
Tim

[RCorbeil · Accepted solution]

It depends on what you're after. If you want the average time in something other than days

Convert(Decimal(10,4),Avg(Cast(DateDiff(ss, htblticket.date, InitialResponseDate.FirstPublicReply) As decimal) / 86400)) As AverageDays

you'll want to replace 86400 (that is, 60x60x24) with either 3600 (60x60) for average hours or 60 for average minutes. (For future legibility, you might consider replacing 86400 with 60*60*24, for instance, so when you come back to the code later you don't have to wonder where the number 86400 came from.)

I don't have helpdesk data in my database so I can't verify that it works, but it looks to me from your existing query that

Avg(Cast(DateDiff(ss, htblticket.date, InitialResponseDate.FirstPublicReply) As decimal))

is the elapsed time, so you should be able to substitute that in if you want the days/hours/minutes spelled out.

  Convert(nvarchar(10), Ceiling(Floor(Convert(integer, Avg(Cast(DateDiff(ss, htblticket.date, InitialResponseDate.FirstPublicReply) As decimal))) / 3600 / 24))) + ' days, '
+ Convert(nvarchar(10), Ceiling(Floor(Convert(integer, Avg(Cast(DateDiff(ss, htblticket.date, InitialResponseDate.FirstPublicReply) As decimal))) / 3600 % 24))) + ' hours, '
+ Convert(nvarchar(10), Ceiling(Floor(Convert(integer, Avg(Cast(DateDiff(ss, htblticket.date, InitialResponseDate.FirstPublicReply) As decimal))) % 3600 / 60))) + ' minutes' AS [Elapsed Time]

[Tholmes]

RC62N wrote:
It depends on what you're after. If you want the average time in something other than days

Convert(Decimal(10,4),Avg(Cast(DateDiff(ss, htblticket.date, InitialResponseDate.FirstPublicReply) As decimal) / 86400)) As AverageDays

you'll want to replace 86400 (that is, 60x60x24) with either 3600 (60x60) for average hours or 60 for average minutes. (For future legibility, you might consider replacing 86400 with 60*60*24, for instance, so when you come back to the code later you don't have to wonder where the number 86400 came from.)

I don't have helpdesk data in my database so I can't verify that it works, but it looks to me from your existing query that

Avg(Cast(DateDiff(ss, htblticket.date, InitialResponseDate.FirstPublicReply) As decimal))

is the elapsed time, so you should be able to substitute that in if you want the days/hours/minutes spelled out.

  Convert(nvarchar(10), Ceiling(Floor(Convert(integer, Avg(Cast(DateDiff(ss, htblticket.date, InitialResponseDate.FirstPublicReply) As decimal))) / 3600 / 24))) + ' days, '
+ Convert(nvarchar(10), Ceiling(Floor(Convert(integer, Avg(Cast(DateDiff(ss, htblticket.date, InitialResponseDate.FirstPublicReply) As decimal))) / 3600 % 24))) + ' hours, '
+ Convert(nvarchar(10), Ceiling(Floor(Convert(integer, Avg(Cast(DateDiff(ss, htblticket.date, InitialResponseDate.FirstPublicReply) As decimal))) % 3600 / 60))) + ' minutes' AS [Elapsed Time]

Thank you very much
Tim

[RCorbeil]

Are you asking how to display the elapsed time as hours or minutes versus days?

If yes, given 86,400s = 1 day = (60s x 60min x 24h), instead of dividing the elapsed number of seconds by (60x60x24), if you wanted the result in hours, you would divide by (60x60). If you wanted it in minutes, you would divide the elapsed seconds by 60.

elapsed seconds / (60x60x24) = elapsed days
elapsed seconds / (60x60) = elapsed hours
elapsed seconds / (60) = elapsed minutes

If you're asking how to display it as days, hours, and minutes, I believe this should do that:

  Convert(nvarchar(10), Ceiling(Floor(Convert(integer, elapsed_seconds) / 3600 / 24))) + ' days, '
+ Convert(nvarchar(10), Ceiling(Floor(Convert(integer, elapsed_seconds) / 3600 % 24))) + ' hours, '
+ Convert(nvarchar(10), Ceiling(Floor(Convert(integer, elapsed_seconds) % 3600 / 60))) + ' minutes'

[Tholmes]

RC62N wrote:
Are you asking how to display the elapsed time as hours or minutes versus days?

If yes, given 86,400s = 1 day = (60s x 60min x 24h), instead of dividing the elapsed number of seconds by (60x60x24), if you wanted the result in hours, you would divide by (60x60). If you wanted it in minutes, you would divide the elapsed seconds by 60.

elapsed seconds / (60x60x24) = elapsed days
elapsed seconds / (60x60) = elapsed hours
elapsed seconds / (60) = elapsed minutes

If you're asking how to display it as days, hours, and minutes, I believe this should do that:

  Convert(nvarchar(10), Ceiling(Floor(Convert(integer, elapsed_seconds) / 3600 / 24))) + ' days, '
+ Convert(nvarchar(10), Ceiling(Floor(Convert(integer, elapsed_seconds) / 3600 % 24))) + ' hours, '
+ Convert(nvarchar(10), Ceiling(Floor(Convert(integer, elapsed_seconds) % 3600 / 60))) + ' minutes'

Thank you very much for the calculations, looks awesome, erm, I am not sure where to put these, where I thought, just generated the lovely red wheel of stupidity on Lansweeper, about 7 times, so thought I would ask where to place and what segment to replace.