yelyah.nodrog Posted December 18, 2017 Posted December 18, 2017 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
Victor Posted December 18, 2017 Posted December 18, 2017 @yelyah.nodrog 17 minutes ago, yelyah.nodrog said: h_datelogged < DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND (NOW() - INTERVAL 60 DAY I think for 90-60 and 60-30 you meant to use BETWEEN: h_datelogged BETWEEN DATE_SUB(CURDATE(), INTERVAL 90 DAY) AND DATE_SUB(CURDATE(), INTERVAL 60 DAY) h_datelogged BETWEEN DATE_SUB(CURDATE(), INTERVAL 60 DAY) AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) For the 91+ you are using the "less than" operator, you need "greater than": h_datelogged > DATE_SUB(CURDATE(), INTERVAL 90 DAY)
yelyah.nodrog Posted December 20, 2017 Author Posted December 20, 2017 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.
Victor Posted December 20, 2017 Posted December 20, 2017 @yelyah.nodrog of course, how could I miss that... you need the incidents that have been logged more than 90 days ago which are still open... so I was wrong, the operator is not "greater than" (>) it is "less than" (<) So the filter would be: h_datelogged < DATE_SUB(CURDATE(), INTERVAL 90 DAY)
yelyah.nodrog Posted December 20, 2017 Author Posted December 20, 2017 Perfect! to be honest i should have noticed that too... Thankyou as always victor
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