Hi
May I kindly get assistants with the query below. Can the query be simplified in order to pull open cases from the Database?
The purpose of this exercise it to get the same amount of open cases from the Admin console as well as the database (Daily Statistics).
CA query found in the logs
Select /*+ index ( arrfcaseauditlog ARRFCASEAUDITLOG_DTCREATED_IDX ) */ SUM( case when prevcasestatus in(0,1) and casestatus=3 THEN 1 ELSE 0 END) as CASESOPENICH, SUM( case when casestatus = 4 and workflow = 4 THEN 1 ELSE 0 END) as CASESCLOSEDICH, SUM( case when workflow = 4 THEN 1 ELSE 0 END) as ACTIVITYCOUNTICH, to_char(DTCREATED + 120/1440,'MM/dd/yyyy - Dy') as PERIOD from arrfcaseauditlog where (DTCREATED BETWEEN to_date('05/31/2018 22:00:00', 'mm/dd/yyyy hh24:mi:ss') AND to_date('06/30/2018 21:59:59', 'mm/dd/yyyy hh24:mi:ss') ) AND ORGNAME=? AND txid is null group by to_char(DTCREATED + 120/1440,'MM/dd/yyyy - Dy') order by PERIOD
My query
SELECT COUNT(DISTINCT(USERNAME)) FROM ARRFCASEAUDITLOG
WHERE DTCREATED > TO_DATE ('2017-06-20 00:00', 'YYYY-MM-DD HH24:MI') AND DTCREATED < TO_DATE ('2017-06-20 23:59', 'YYYY-MM-DD HH24:MI')
AND CASESTATUS = 2;
My query does not return the same number of open cases compared to the admin console.
Regards;
Neo