Average time to resolve report - all techs

Average time to resolve report - all techs

Hello - searching the forums here I'm just not quite finding the exact query I'm looking for. We recently migrated to ServiceDesk Plus from our old on prem spiceworks. 

Needing help creating a simple Average time to resolve query, this is what I used to run in Spiceworks that used SQLite:

SELECT DATE(Created_at, 'Start of month') as 'Start of month', 
ROUND(avg(julianday(ifnull(closed_at,'now'))-julianday(created_at)),2) 
AS "Time to Resolve" FROM tickets where priority = '2'
GROUP BY DATE(tickets.created_at, 'Start of month')



Perhaps someone could assist converting this to work in SDP? We are running the PGSQL database option. 

I am basically just looking to run a query each month to track and report on our "average time to close" for medium priority service requests. Need the average time to be expressed in days. Typically I have to run this for 12 months at a time and do a look back as when tickets were closed that spanned multiple months that would impact the report calculations. 
Thanks much for any feedback


            Related Products