yelyah.nodrog

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?/

Apologies this should only read: AND (month(h_dateresolved) = (Month(CURDATE()) - 1) and year(h_dateresolved)=year(curdate()))

great! lol, i changed it alot since

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?

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.

Perfect! to be honest i should have noticed that too... Thankyou as always victor

Hey Victor! thanks for this, it appears to work for the 30-60 and 60-90 but the 90+ is giving me a large number of outstanding calls which from views and reports i can verify is not correct? This is what i have for the widget 90+ and incident , This is the result i am getting... which is too high, because we dont have that many calls outstanding? Hayley.

I have written a widget for Aged calls I would like to know the Aged calls for 30-60 days, 60-90 days and 90+ days This is what I have for 30-60: (h_status = 'status.New' OR h_status = 'status.open' OR h_status = 'status.onhold') and h_requesttype = 'incident' and h_datelogged < DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND (NOW() - INTERVAL 60 DAY) AND (h_fk_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/AV/AVTEAM/' OR h_fk_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/SDEP/ENPT/' OR h_fk_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/SDEP/SERDSK/') for 60-90: (h_status = 'status.New' OR h_status = 'status.open' OR h_status = 'status.onhold') and h_requesttype = 'incident' and h_datelogged < DATE_SUB(CURDATE(), INTERVAL 60 DAY) AND (NOW() - INTERVAL 90 DAY) AND (h_fk_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/AV/AVTEAM/' OR h_fk_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/SDEP/ENPT/' OR h_fk_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/SDEP/SERDSK/') and 90+: (h_status = 'status.New' OR h_status = 'status.open' OR h_status = 'status.onhold') and h_requesttype = 'incident' and h_datelogged < DATE_SUB(CURDATE(), INTERVAL 90 DAY) AND (h_fk_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/AV/AVTEAM/' OR h_fk_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/SDEP/ENPT/' OR h_fk_team_id = 'GOSH/DivFINICT/SubICT/ITSRV/SDEP/SERDSK/') However I am sure that calls are repeating - so calls that are 90 days old are showing up in 30-60 and 60-90 days. I changed the internals to 30-60, 61-9, 91+ but it doesn't seem to have changed anything? are there any suggestions? Thanks

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

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?

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

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.

Our trust is placing a "change Freeze" in place over the xmas week and we need a way to block out dates in the change calendar so none are scheduled. From the 15th Dec to 1st Jan. I have had a look at previous topics and the only thing I can find is: However this was last year. and I am wondering if there has been any update? and if not if anything can be advised as a work around? Hayley.

Hey Victor, I have given this a go but it is not reporting correctly? Today is the start of a new week and we have logged 16 calls so far this morning, however its not showing any as being logged? - I have refreshed it to make sure and still nothing. This is the code we are currently using: These are the logged calls so far today

That works perfectly Victor, thankyou, is there a way we could apply this to count the calls logged in a week? so Monday to Sunday?

I have tried again using this:

Morning all, I am trying to create a widget to tell me the current calls logged this month so far. so from the 1st nov to current date, I don't want to have to change the query every month to keep up and want it to roll each month. So I have written this (I'm quite new to T-sql so it wont be right!): (h_requesttype = 'Service Request' OR h_requesttype = 'Incident') AND DATE(h_datelogged) BETWEEN dateadd(DAY,-(DATEPART(DAY,GETDATE())-1),GETDATE()) AND GETDATE()) however its not working, is this anywhere near right? Could someone point me in the correct direction with this query, also is there a easier way to do it? Thanks Hayley

Can I please request it as a possible enhancement then?, as at the moment it is only emailing to a single person (the call owner) or the call owners manager, this really is creating a single point of failure, as if the manager isn't in, no one knows it has breached?

thanks victor this has now been resolved

perfect thanks bob!

Hey Victor, Thanks for getting back to me, unfortunately when I look in this option it only gives me the option to escalate to the owner or owners manager... what we really need to a way to send this to a agreed distribution list, is there a way I can do this? I cant seem to see a option? Hayley.

Morning, Quick question We have recently starting having issues when sending out a email to our Change stakeholders distribution list on our changes, we are getting a message advising: mail Update by unknown sender (administrator@gosh.nhs.uk) Undeliverable: The Change CH00024674 has been implemented Delivery has failed to these recipients or groups: ChangeStakeholders@gosh.nhs.uk Your message can't be delivered because delivery to this address is restricted. Diagnostic information for administrators: Where is it getting the address administrator@gosh.nhs.uk? I think its restricted due to something happening to the distribution list as it advises delivery to the address is restricted, however it is also advising that the administrator@gosh.nhs.uk email address is a unknown sender and I cant remember ever setting anything like it up? Can someone tell me where this is and why it is showing as this? Please see attached email with more errors: RE Request CH00024674 has been updated via email.msg Thanks Hayley.

Can you please advise where the screen mentioned is? Thanks, Hayley.

that worked! your a star! thankyou!

Morning, I had a few questions around Breached email templates and notifications. At what point does the system send out a Breached template and how does it know who to send it too? I'm assuming it looks at the person/team that raised the call and finds out the managers of that person/team from the field: Home > System > Organisational Data > Users, then the about tab and the Manager field? If this is the case we are finding that half these fields are incorrect and this is down to the system its pulling the information in from being incorrect.... Unfortunately at this tie this is not something we are looking to correct, so I wanted to know if there is any way to stop the breach notifications going out in this fashion.....we still want breach notification (templates) however we would like them to go to a set distribution group.... is this possible? if not is there a way to at least turn this feature off for now? Hayley.