yelyah.nodrog Posted December 18, 2017 Posted December 18, 2017 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 Posted December 18, 2017 Author Posted December 18, 2017 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 Posted December 18, 2017 Author Posted December 18, 2017 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 Posted December 18, 2017 Author Posted December 18, 2017 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 Posted December 18, 2017 Posted December 18, 2017 @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 Posted December 20, 2017 Posted December 20, 2017 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 Posted December 20, 2017 Author Posted December 20, 2017 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 Posted December 20, 2017 Posted December 20, 2017 @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 Posted December 20, 2017 Author Posted December 20, 2017 great! lol, i changed it alot since
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now