I want to change the built in report to show hours and not just days

[Tholmes]

So, I basically copy and paste all my reports, I don't know anything about SQL. I have jsut pasted the conversion from days to hours and mins etc. the past few times I used it, but now I need some assistance, I wanted to change the period from just days to days, hours and minutes.
Google gets complex very fast, when you google this error.
Here is my code so far, but it says "Arithmetic overflow error converting expression to data type int."


Select Top 1000000 DatePart(yyyy, htblticket.date) As Year,
DatePart(mm, htblticket.date) As Month,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Avg(Cast(DateDiff_big(ss,
htblticket.date, htblticket.ticketid) As decimal))) / 3600 / 24))) +
' days, ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Avg(Cast(DateDiff_big(ss,
htblticket.date, htblticket.ticketid) As decimal))) / 3600 % 24))) +
' hours, ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Avg(Cast(DateDiff_big(ss,
htblticket.date, htblticket.ticketid) As decimal))) % 3600 / 60))) +
' minutes' As [Elapsed Time]
From htblticket
Inner Join (Select Top 1000000 htblticket.ticketid,
Max(htblhistory.date) As CloseDate
From htblticket
Inner Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htblhistory On htblhistory.ticketid = htblticket.ticketid
Inner Join htblticketstates htblticketstates1 On
htblhistory.ticketstateid = htblticketstates1.ticketstateid
Inner Join htblhistorytypes On htblhistorytypes.typeid =
htblhistory.typeid
Where htblticketstates.statename = 'Closed' And
htblticketstates1.statename = 'Closed' And htblhistorytypes.name
In ('Status changed', 'Note added and state changed',
'Internal note added and state changed')
Group By htblticket.ticketid) As ClosedDate On ClosedDate.ticketid =
htblticket.ticketid
Where htblticket.spam <> 'True'
Group By DatePart(yyyy, htblticket.date),
DatePart(mm, htblticket.date)
Order By Year Desc,
Month Desc

[RCorbeil]

It's first thing in the morning and I haven't consumed enough caffeine. It looks like the report you're trying to modify is using the averages, so what I offered up won't be exactly what you're after. The key element applies, however: you want to calculate the difference between the start and close times, not the start time and the ticket ID.

DateDiff(ss, htblticket.date, ClosedDate.CloseDate)

rather than

DateDiff_big(ss, htblticket.date, htblticket.ticketid)

[Tholmes]

You answered it, thanks a million, just what I needed

RC62N wrote:
It's first thing in the morning and I haven't consumed enough caffeine. It looks like the report you're trying to modify is using the averages, so what I offered up won't be exactly what you're after. The key element applies, however: you want to calculate the difference between the start and close times, not the start time and the ticket ID.

DateDiff(ss, htblticket.date, ClosedDate.CloseDate)

rather than

DateDiff_big(ss, htblticket.date, htblticket.ticketid)

[RCorbeil · Accepted solution]

We don't use the helpdesk module at my site, so I don't have any data to experiment with, but looking at your query I am puzzled by what you're doing to calculate the elapsed time:

Avg(Cast( DateDiff_big(ss, htblticket.date, htblticket.ticketid) As decimal ))

From the data dictionary, htblticket.date is a datetime value, the date/time that a ticket was created. htblticket.ticketid is an integer, a unique ID assigned to the table record. It's not a date/time.

Two question: why are you trying to calculate the difference between the date/time the ticket was created and the ticket ID and why are you taking an average of the result? You should be referencing the ticket close date rather than the ticket ID. The average leaves me puzzled. Perhaps it's a remnant of another report you sourced to try to build this one?

It looks like you've already incorporated a join to link in the close date, you're just not referencing it in your calculation. From the report "Helpdesk: Average time until ticket set to Closed, by month", where I assume you sourced some of your report code:

DateDiff(ss, htblticket.date, ClosedDate.CloseDate)

I'm guessing what you're after is:

Select Top 1000000
  DatePart(yyyy, htblticket.date) As Year,
  DatePart(mm, htblticket.date) As Month,
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer, DateDiff(ss, htblticket.date, ClosedDate.CloseDate)) / 3600 / 24))) + ' days, ' +
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer, DateDiff(ss, htblticket.date, ClosedDate.CloseDate)) / 3600 % 24))) + ' hours, ' +
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer, DateDiff(ss, htblticket.date, ClosedDate.CloseDate)) % 3600 / 60))) + ' minutes' As [Elapsed Time]

or something close to that.