Report showing timescales for resolving requests

[mojahidm]

Hi Team,

I'm currently working on a report where we need to know the timescale of priority 3 tickets being resolved

i.e.

how many tickets took a week to resolve

how many took a month

how many took 6 months

and how many took longer than 6 months

I have the following SQL that I ran in database direct and it works as I would want it:

select h_pk_reference, h_fk_servicelevelagreementname,
case 
when h_fixtime between 0 and 16200 then 'Less than a week'
when h_fixtime between 16201 and 648000 then 'Less than a month'
when h_fixtime between 648001 and 3888000 then 'less than six months'
else 'more than six months'
end as P3Timescale
from h_itsm_requests
where h_requesttype = 'Incident'
and h_container_id between 88 and 104
and h_fk_priorityname = 'Priority 3'
and h_status in ('status.resolved', 'status.closed')
order by P3Timescale

But is there a way of getting this to work in a report?

I could also do with having this as a measure/widget if possible.

Many thanks,

Mojahid

[Victor]

@mojahidm - You should be able to do this with a custom SQL widget

[mojahidm]

Thanks @Victor :-)