Queries to fetch the job runs for a particular time period

[Sri Krishna Munjuluri]

Hi Team 

Could you please review and let me know if the below query is appropriate for fetching job run counts in the environment for a specific period?

The reason I ask is that we occasionally observe a delta in results each time we run the query, and I'd like to validate the logic being used.

SELECT  SUM(count(*)) FROM CAWAADM.esp_generic_job a, CAWAADM.esp_application b
WHERE   a.appl_id = b.appl_id 
-- Save Specific Time Period Conditions
AND (a.start_date_time >= TO_DATE('03-21-2025 00:00:00','MM-DD-YYYY HH24:MI:SS'))
AND (a.start_date_time <= TO_DATE('04-20-2025 23:59:59','MM-DD-YYYY HH24:MI:SS'))
GROUP BY b.APPL_NAME
ORDER BY b.APPL_NAME

------------------------------
Sri Krishna
------------------------------

[Nitin Pande]

Hi,

If you just want the count of jobs, then you can try this:

SELECT COUNT(job_id)::int FROM esp_generic_job 
where (start_date_time >= TO_DATE('03-21-2025 00:00:00','MM-DD-YYYY HH24:MI:SS'))

HTH,

Nitin Pande

------------------------------
Support
Broadcom
Toronto
------------------------------

[Sri Krishna Munjuluri]

Hey, Nitin thank you this worked and I used it in below way.

SELECT COUNT(job_id) FROM CAWAADM.ESP_GENERIC_JOB
where 
start_date_time >= TO_DATE('03-21-2025 00:00:00','MM-DD-YYYY HH24:MI:SS')
AND 
(start_date_time <= TO_DATE('04-20-2025 23:59:59','MM-DD-YYYY HH24:MI:SS'))

[Nitin Pande]

Hi Sri Krishna,

Glad it worked.  I should have pointed out that my solution was tested on PostgreSQL.  I'm guessing yours is for Oracle.

Nitin Pande

------------------------------
Support
Broadcom
Toronto
------------------------------

[Sri Krishna Munjuluri]

Yes , we're on Oracle.