yelyah.nodrog Posted December 20, 2017 Posted December 20, 2017 I have written a widget to show me calls resolved for the previous month (nov) for a specific team with columns for Priority, Month Avg Response time, Avg Resolve Time , Avg Hold time and ticket count. This is working fine however i would like it to include Closed calls as some of our calls close without a response or resolve time (quick closures) The SQL i have for the widget currently is 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())) OR (month(h_dateresolved) = (Month(CURDATE()) - 1) and year(h_dateresolved)=year(curdate()))) AND (h_resolvedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/SRVRAD/' OR h_resolvedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/STRGBKUP/' OR h_resolvedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/APPDEL/' OR h_resolvedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/DOMAD/' OR h_resolvedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/EXCHNG/') GROUP BY month(h_dateresolved), h_fk_priorityname This works however when i try to add in the ClosedBy bit it fails 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/DTCNTR/SRVRAD/' OR h_resolvedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/STRGBKUP/' OR h_resolvedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/APPDEL/' OR h_resolvedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/DOMAD/' OR h_resolvedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/EXCHNG/')OR (h_closedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/SRVRAD/' OR h_closedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/STRGBKUP/' OR h_closedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/APPDEL/' OR h_closedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/DOMAD/' OR h_closedby_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/EXCHNG/'))) GROUP BY month(h_dateresolved), h_fk_priorityname Adding the h_closedby_team_id bit is working fine but im struggling to get this bit to work with closed aswell ((month(h_dateresolved) = (Month(CURDATE()) - 1) and year(h_dateresolved)=year(curdate())) I have tried (((month(h_dateresolved) = (Month(CURDATE()) - 1) and year(h_dateresolved)=year(curdate())) OR ((month(h_dateclosed) = (Month(CURDATE()) - 1) and year(h_dateclosed)=year(curdate())) ) Aswell as (((month(h_dateresolved) OR month(h_dateclosed)) = (Month(CURDATE()) - 1)) and ((year(h_dateresolved) OR year(h_dateclosed)) =year(curdate()))) but they are not working, i imagine something is wrong in my code but i dont know what? Does anyone have any ideas? Hayley.
yelyah.nodrog Posted December 20, 2017 Author Posted December 20, 2017 1 hour ago, yelyah.nodrog said: AND ((month(h_dateresolved) = (Month(CURDATE()) - 1) and year(h_dateresolved)=year(curdate())) OR (month(h_dateresolved) = (Month(CURDATE()) - 1) and year(h_dateresolved)=year(curdate()))) Apologies this should only read: AND (month(h_dateresolved) = (Month(CURDATE()) - 1) and year(h_dateresolved)=year(curdate()))
Victor Posted December 20, 2017 Posted December 20, 2017 @yelyah.nodrog can you explain a bit what do you mean by "they are not working"? Is it not the correct or expected results?
yelyah.nodrog Posted December 20, 2017 Author Posted December 20, 2017 sorry victor when i put the bit: (((month(h_dateresolved) = (Month(CURDATE()) - 1) and year(h_dateresolved)=year(curdate())) OR ((month(h_dateclosed) = (Month(CURDATE()) - 1) and year(h_dateclosed)=year(curdate())) ) I would expect it to give me a list of calls resolved or closed for the previous month, however the result i am getting also has the month before in it? - see my lovely blue circle :D, I am also not sure it is truly representing all calls closed or resolved in November for the teams selected in the query. Hope that helps?/
Victor Posted December 20, 2017 Posted December 20, 2017 @yelyah.nodrog right so, this bit: (MONTH(h_dateresolved)=MONTH(CURDATE()-1 AND YEAR(h_dateresolved)=YEAR(curdate())) OR (MONTH(h_dateclosed)=MONTH(CURDATE())-1 AND YEAR(h_dateclosed)=YEAR(CURDATE())) What this bit actually doing is returning results where the resolved date is in last month OR closed date is in last month. The important bit is the "OR" operator, because you can have requests resolved in Oct but closed in Nov... and because of the "OR" they are included in the result set. And since you are reporting resolved date it will display Oct... What exactly is the criteria for these closed calls? You mentioned "some of our calls close without a response or resolve time (quick closures)" but all requests will have a resolved date... Are you referring to the SLA response and resolve timers (as in these requests don't have one)? If yes, then you don't need the "(MONTH(h_dateclosed) = MONTH(CURDATE())-1 AND YEAR(h_dateclosed) = YEAR(CURDATE()))" bit... if they are quick closures the resolved and close date will be pretty much the same...
yelyah.nodrog Posted December 20, 2017 Author Posted December 20, 2017 basically i am trying to capture our quick log/quick closure calls: This is the bpm for them. Which doesn't have a resolved start or resolved stop timer in it because whenever i put one it it breaks it. Unless you can tell me exactly where to put it and how to put it in without breaking it? always comes up with a bunch of errors on the call? So as some of our calls have resolve times and some dont. to capture all of the calls our analysts are creating i need to capture all calls resolved in the previous month. Aswell as any calls closed in the prevous month without a resolve timer? does that make sense. I realise its a mess, but i haven't had the time to play with it properly to hopefully fix it? Hayley. Hayley.
yelyah.nodrog Posted December 20, 2017 Author Posted December 20, 2017 Whoops dont worry i have figured it out
Victor Posted December 20, 2017 Posted December 20, 2017 1 minute ago, yelyah.nodrog said: any calls closed in the prevous month without a resolve timer @yelyah.nodrog yes it, does make sense and is the scenario I thought of... so, just to clear any misunderstanding between SLA Resolve Timer and Request Resolved Date: The resolve timer represents a target date for a request to be resolved The resolved date represents the actual date when the request has been resolved Now, in your scenario, you have a BP closing a request automatically. Which means the request skips the "Resolve" sequence (this is not the case for manual action if actioned manually a request needs to be resolved first before it can be closed). So, you need to cater for requests with no resolve date and with a closed date last month. So the filter should be: (h_dateresolved IS NOT NULL AND MONTH(h_dateresolved) = MONTH(CURDATE()) - 1 AND YEAR(h_dateresolved) = YEAR(curdate()) OR (h_dateresolved IS NULL AND MONTH(h_dateclosed) = MONTH(CURDATE()) - 1 AND YEAR(h_dateclosed) = YEAR(curdate()) However, now you also need to cater for an empty resolved date and use closed date instead in the grouping so the GROUP BY criteria becomes: GROUP BY (CASE WHEN h_dateresolved IS NULL THEN MONTH(h_dateclosed) ELSE MONTH(h_dateresolved) END), h_fk_priorityname Basically, with a bit of cleanup (bit more brackets and OR operators used than needed) the whole query is: SELECT h_fk_priorityname 'Priority', MONTH(h_dateresolved) 'Month', CONCAT(LPAD(FLOOR(AVG(h_responsetime)/3600),2,'0'),':',FLOOR(MOD(AVG(h_responsetime),3600)/60)) 'Avg Response', CONCAT(LPAD(FLOOR(AVG(h_fixtime)/3600),2,'0'),':',FLOOR(MOD(AVG(h_fixtime),3600)/60)) 'Avg Resolve', CONCAT(LPAD(FLOOR(AVG(h_totalonholdtime)/3600),2,'0'),':',FLOOR(MOD(AVG(h_totalonholdtime),3600)/60)) 'Avg OnHold', COUNT(h_pk_reference) 'Tickets' FROM h_itsm_requests WHERE h_status IN ('status.closed', 'status.resolved') AND h_fk_priorityname NOT LIKE 'PM%' AND h_fk_priorityname NOT LIKE 'R99%' AND ((h_dateresolved IS NOT NULL AND MONTH(h_dateresolved) = MONTH(CURDATE()) - 1 AND YEAR(h_dateresolved) = YEAR(curdate())) OR (h_dateresolved IS NULL AND MONTH(h_dateclosed) = MONTH(CURDATE()) - 1 AND YEAR(h_dateclosed) = YEAR(curdate()))) AND (h_resolvedby_team_id IN ('GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/SRVRAD/', 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/STRGBKUP/', 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/APPDEL/', 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/DOMAD/', 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/EXCHNG/') OR h_closedby_team_id IN ('GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/SRVRAD/', 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/STRGBKUP/', 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/APPDEL/', 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/DOMAD/', 'GOSH/DivFINICT/SubICT/ITSRV/DTCNTR/EXCHNG/')) GROUP BY (CASE WHEN h_dateresolved IS NULL THEN MONTH(h_dateclosed) ELSE MONTH(h_dateresolved) END), h_fk_priorityname
Victor Posted December 20, 2017 Posted December 20, 2017 4 minutes ago, yelyah.nodrog said: Whoops dont worry i have figured it out Oh well... I hope my post is useful for further reference then
yelyah.nodrog Posted December 20, 2017 Author Posted December 20, 2017 yours is prettier so ill keep it!
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