Jump to content
yelyah.nodrog

Widget for Resolved OR Closed

Recommended Posts

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.

Capture.PNG.f936bdd0b7b412d073b8103a77a7b19e.PNG

 

Share this post


Link to post
Share on other sites
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()))

 

Share this post


Link to post
Share on other sites

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.

Capture.PNG.ac253b006826a1f784f25b0992e2fa75.PNG

Hope that helps?/

 

Share this post


Link to post
Share on other sites

@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...

Share this post


Link to post
Share on other sites

basically i am trying to capture our quick log/quick closure calls: This is the bpm for them.

Capture.thumb.PNG.6befbcd958158d176498e10958b7a8de.PNG

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.

Share this post


Link to post
Share on other sites
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

 

Share this post


Link to post
Share on other sites
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 :)

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...