Exclude Deleted Requests from Pending Requests Report

Hello,

I have a custom report that groups pending requests into age bands. Over time, we have noticed that the "Total Pending Requests" is somewhat higher than the real total. Having simplified the query, the reason is that Deleted Requests are included in this total, which retain the status "Open".

I am unable to find the relevant table or flag in the database to use in this query, to exclude Deleted Requests. Could you offer some advice please?

Here is the query:

SELECT count(WORKORDER.WORKORDERID) "Total Pending Count",
COUNT(CASE WHEN DATE_PART('day',now()::timestamp - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)::timestamp) <= 5 THEN 1
ELSE NULL END) "0 - 5 Days",
COUNT(CASE WHEN DATE_PART('day',now()::timestamp - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)::timestamp) <= 10
and DATE_PART('day',now()::timestamp - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)::timestamp) > 5 THEN 1
ELSE NULL END) "5 - 10 Days",
COUNT(CASE WHEN DATE_PART('day',now()::timestamp - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)::timestamp) <= 30
and DATE_PART('day',now()::timestamp - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)::timestamp) > 10 THEN 1 ELSE NULL END) "10 - 30 Days",
COUNT(CASE WHEN DATE_PART('day',now()::timestamp - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)::timestamp) <= 60
and DATE_PART('day',now()::timestamp - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)::timestamp) > 30 THEN 1
ELSE NULL END) "30 - 60 Days",
COUNT(CASE WHEN DATE_PART('day',now()::timestamp - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)::timestamp) <= 90
and DATE_PART('day',now()::timestamp - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)::timestamp) > 60 THEN 1
ELSE NULL END) "60 - 90 Days",
COUNT(CASE when DATE_PART('day',now()::timestamp - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)::timestamp) > 90
THEN 1 ELSE NULL END) "More than 90 Days" FROM WORKORDER
INNER JOIN WORKORDERSTATES on WORKORDERSTATES.WORKORDERID = WORKORDER.WORKORDERID
LEFT JOIN STATUSDEFINITION on STATUSDEFINITION.STATUSID = WORKORDERSTATES.STATUSID
LEFT JOIN WorkOrder_Queue woq ON WORKORDER.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
WHERE StatusDefinition.ISPENDING='1'
ORDER BY 1

The problem has been exacerbated by the fact that Deleted Requests are not being deleted after 7 days as they should be (and used to be). See below screenshot. I have not looked into this further, but I presume this is a bug introduced in the current version we are running (14304) and is likely fixed with an update. It is not an issue anyway, but I do need to exclude these "Open" deleted items from the Pending Request report. Of course, I can delete them from Trash manually but the problem will occur again.