### Notification Error

# Helpdesk report, average time ticket response, need times in hours not days - I would like this report in hours and mins not fractions of days

Posted: Friday, February 19, 2021 1:30:54 AM(UTC) ## TimHolmes1973

Member Original PosterPosts: 34 0
Like This issue has been solved! Click here to view the solution
I found this report here
https://www.lansweeper.c...cket-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
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.
Code:
```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:
Code:
```
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'```
Originally Posted by: RC62N 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.
Code:
```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:
Code:
```
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.
It depends on what you're after. If you want the average time in something other than days
Code:
`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
Code:
`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.
Code:
```  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]```
Originally Posted by: RC62N It depends on what you're after. If you want the average time in something other than days
Code:
`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
Code:
`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.
Code:
```  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