Jump to content

Ages Calls duplications


Recommended Posts

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

 

Link to post
Share on other sites

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

 

Link to post
Share on other sites

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

Capture.PNG.952e16c8e947a530c583d9e4bb955272.PNG,

This is the result i am getting...

Capture1.PNG.0d09b8f2a595347fd9ed99d4c9922066.PNG

which is too high, because we dont have that many calls outstanding?

Hayley.

Link to post
Share on other sites

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

 

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