Notification

Icon
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

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
RC62N
#1RC62N Member Posts: 522  
posted: 2/24/2021 4:33:25 PM(UTC)
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'
TimHolmes1973
#2TimHolmes1973 Member Original PosterPosts: 34  
posted: 2/24/2021 5:20:38 PM(UTC)
Originally Posted by: RC62N Go to Quoted Post
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.
RC62N
#3RC62N Member Posts: 522  
posted: 2/24/2021 6:04:43 PM(UTC)
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]
TimHolmes1973
#4TimHolmes1973 Member Original PosterPosts: 34  
posted: 2/24/2021 11:07:26 PM(UTC)
Originally Posted by: RC62N Go to Quoted Post
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

Active Discussions

Lansweeper Verify a successful copy to user computer
by  Brandon   Go to last post Go to first unread
Last post: Yesterday at 9:09:50 PM(UTC)
Lansweeper Pablo
by  Pablo  
Go to last post Go to first unread
Last post: 4/20/2021 8:05:07 PM(UTC)
Lansweeper Can Actions work when using when using a Ubuntu machine
by  mzipperer   Go to last post Go to first unread
Last post: 4/1/2021 10:16:34 PM(UTC)
Lansweeper Remove user from local admin group
by  jhartley@alcona-county.net  
Go to last post Go to first unread
Last post: 3/24/2021 4:27:30 PM(UTC)
Action Password Status
by  mcgr5   Go to last post Go to first unread
Last post: 3/24/2021 2:07:51 PM(UTC)
Lansweeper Network Scanning
by  MZU  
Go to last post Go to first unread
Last post: 3/7/2021 8:23:10 AM(UTC)
Lansweeper Creating a schduled task on remote computer
by  steveb   Go to last post Go to first unread
Last post: 2/26/2021 7:44:48 PM(UTC)
Lansweeper Uninstall Software action not working?
by  Brandon  
Go to last post Go to first unread
Last post: 2/15/2021 10:03:22 PM(UTC)