Average SLA times

[yelyah.nodrog]

I am trying to create a widget that updates the average SLA times per team each month, I am aware I can do it as a measure and then put it into a widget however this will take me twice as long.

Is there a way to create a widget so I can see the average SLA times for our p1, p2, p3 and p4 SLAS

This is the SQL I have written so far:
SELECT h_requesttype, h_dateresolved, h_resolvedby_team_id, h_status, h_fk_priorityname, h_withinfix, AVG(h_fixtime) as AverageResolveTime
FROM h_itsm_requests
WHERE h_fk_priorityname= 'p4'
AND (h_status= 'status.closed' OR h_status= 'status.resolved')
AND h_withinfix= '1'
AND (h_dateresolved >= (CURDATE() - INTERVAL (Month(CURDATE())) DAY))
AND h_requesttype ='incident'
GROUP BY h_fk_priorityname

However I cannot get the Convert or Declare functions to work, I need to get a average resolve time (h_fixtime) result for all P4's in the month of December so far. However it is only giving me the average for the 06/12/2017
does anyone know what I can do so that it gives me a average resolve time for all p4's in December and for it to convert to HH:MM:SS instead of just seconds?

 

Hayley.

[yelyah.nodrog]

I tried this but it is based on T-SQL

SELECT datename(month, h_dateresolved) month, year(h_dateresolved) year, AVG(h_fixtime) as AverageResolveTime

FROM h_itsm_requests

WHERE h_fk_priorityname= 'p4'

AND (h_status= 'status.closed' OR h_status= 'status.resolved')

AND h_withinfix= '1'

AND (month(h_dateresolved) = Month(CURDATE()) and year(h_dateresolved)=year(curdate()))

AND h_requesttype ='incident'

GROUP BY month(h_dateresolved)

 

I could change the datename to date_format but beyond that I don't know what to do..

[yelyah.nodrog]

SELECT h_fk_priorityname, monthname( h_dateresolved) month, ROUND(AVG(h_fixtime)/3600) as ResolveTime
FROM h_itsm_requests
WHERE (h_status= 'status.closed' OR h_status= 'status.resolved')
AND h_withinfix= '1'
AND (month(h_dateresolved) = Month(CURDATE()) and year(h_dateresolved)=year(curdate()))
AND h_requesttype ='incident'
AND (h_resolvedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/AV/AVTEAM/'
OR h_resolvedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/SDEP/ENPT/'
OR h_resolvedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/SDEP/SERDSK/')
GROUP BY month(h_dateresolved), h_fk_priorityname

 

Have now got this far on my own which gives me this!

However I want to either convert the resolve time (H_fixtime) to read HH:MM:SS or at least get it to round to 2 decimal places. so reading 3.40 (3 hrs 40 mins) any ideas?

 

[yelyah.nodrog]

Nevermind, got it.

SELECT h_fk_priorityname AS Priority, monthname( h_dateresolved) month, ROUND(AVG(h_fixtime)/3600,2) as 'Resolve Time'
FROM h_itsm_requests
WHERE (h_status= 'status.closed' OR h_status= 'status.resolved')
AND h_withinfix= '1'
AND (month(h_dateresolved) = Month(CURDATE()) and year(h_dateresolved)=year(curdate()))
AND h_requesttype ='incident'
AND (h_resolvedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/AV/AVTEAM/'
OR h_resolvedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/SDEP/ENPT/'
OR h_resolvedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/SDEP/SERDSK/')
GROUP BY month(h_dateresolved), h_fk_priorityname

 

[Victor]

@yelyah.nodrog just to explain the first statement for future reference:

1 hour ago, yelyah.nodrog said:

Month(CURDATE())

This function returns the month of the specified date. the specified date is the current date, so the MONTH = 12.  This basically means that this will be the criterion for h_dateresolved in the first statement that you tried:

h_dateresolved >= (CURDATE() - INTERVAL (12 DAY))

Today is 18/12, substract 12 days from it and you get the 06/12. Which is the date of the results returned. Happy to see you sorted this though  

[Victor]

On 18/12/2017 at 2:50 PM, yelyah.nodrog said:

so reading 3.40 (3 hrs 40 mins)

@yelyah.nodrog just a quick observation on this... when we talking about time, the number of decimals is not actually the number of minutes. Decimals represent a percentage of the base integer, so to speak, it represents relative, not absolute values.

So, in your widget, when you display hours and have something like 3.40, it actually means 3 hours and 0.4 (or 40%) of an hour. An hour is 60 min so basically, you have 40% of 60 min.

Therefore 3.4 hours = 3 hours and 24 min (not 40 )

[yelyah.nodrog]

even when its like this?

SELECT h_fk_priorityname AS Priority, 
monthname( h_dateresolved) month, concat(lpad(floor(avg(h_responsetime)/3600),2,'0'),':',floor(mod(avg(h_responsetime),3600)/60)) as 'Avg Response', concat(lpad(floor(avg(h_fixtime)/3600),2,'0'),':',floor(mod(avg(h_fixtime),3600)/60)) as 'Avg Resolve',
concat(lpad(floor(avg(h_totalonholdtime)/3600),2,'0'),':',floor(mod(avg(h_totalonholdtime),3600)/60)) as 'Avg OnHold',
 COUNT(h_pk_reference) AS Tickets 
FROM h_itsm_requests
WHERE
(h_status= 'status.closed' OR h_status= 'status.resolved')
AND (h_fk_priorityname NOT LIKE 'PM%')
AND (h_fk_priorityname NOT LIKE 'R99%')
AND (month(h_dateresolved) = (Month(CURDATE()) - 1) and year(h_dateresolved)=year(curdate()))
AND (h_resolvedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/SDEP/SERDSK/' OR h_resolvedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/SDEP/ENPT/' OR h_resolvedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/AV/AVTEAM/')
GROUP BY month(h_dateresolved), h_fk_priorityname

 

This wont represent the correct average times?

[Victor]

@yelyah.nodrog well, you didn't have floor(mod(avg(h_totalonholdtime),3600)/60) in the original post  ... yes, this would work fine!

[yelyah.nodrog]

great! lol, i changed it alot since