Ages Calls duplications

[yelyah.nodrog]

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]

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

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]

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

Perfect! to be honest i should have noticed that too...

Thankyou as always victor

 

 

[Victor]

No worries, happy merry days!