‎02-19-2021 01:30 AM
Solved! Go to Solution.
‎02-24-2021 06:04 PM
Convert(Decimal(10,4),Avg(Cast(DateDiff(ss, htblticket.date, InitialResponseDate.FirstPublicReply) As decimal) / 86400)) As AverageDaysyou'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.)
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]
‎02-24-2021 06:04 PM
Convert(Decimal(10,4),Avg(Cast(DateDiff(ss, htblticket.date, InitialResponseDate.FirstPublicReply) As decimal) / 86400)) As AverageDaysyou'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.)
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]
‎02-24-2021 11:07 PM
RC62N wrote:
It depends on what you're after. If you want the average time in something other than daysConvert(Decimal(10,4),Avg(Cast(DateDiff(ss, htblticket.date, InitialResponseDate.FirstPublicReply) As decimal) / 86400)) As AverageDaysyou'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 thatAvg(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]
‎02-24-2021 04:33 PM
elapsed seconds / (60x60x24) = elapsed days
elapsed seconds / (60x60) = elapsed hours
elapsed seconds / (60) = elapsed minutes
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'
‎02-24-2021 05:20 PM
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'
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now