Count Jobs Submitted After Every Hour

Question

I have a requirement to get the number of jobs that were executed after every hour in Oracle sql.

I have come up with a query to truncate the date to the nearest hour. I'm at a loss on how to write a query that counts the "Job No" occurrences after every hour beginning from midnight.

So far this is my output representing only three columns in the query.

Job No  DateTime            TruncDate
2830161 11/06/2019 00:04:21 11/06/2019 00:00:00
2830162 11/06/2019 00:04:38 11/06/2019 00:00:00
2830163 11/06/2019 00:05:57 11/06/2019 00:00:00
2830164 11/06/2019 00:06:18 11/06/2019 00:00:00
2830168 11/06/2019 01:29:03 11/06/2019 01:00:00
2830169 11/06/2019 01:29:39 11/06/2019 01:00:00
2830170 11/06/2019 01:30:07 11/06/2019 01:00:00
2830175 11/06/2019 01:37:33 11/06/2019 01:00:00
2830186 11/06/2019 02:52:04 11/06/2019 02:00:00
2830193 11/06/2019 03:22:02 11/06/2019 03:00:00
2830194 11/06/2019 03:22:40 11/06/2019 03:00:00
2830199 11/06/2019 03:45:08 11/06/2019 03:00:00
2830201 11/06/2019 04:21:09 11/06/2019 04:00:00
2830202 11/06/2019 04:21:25 11/06/2019 04:00:00
2830203 11/06/2019 04:47:30 11/06/2019 04:00:00
2830204 11/06/2019 04:47:48 11/06/2019 04:00:00
2830205 11/06/2019 07:13:43 11/06/2019 07:00:00
2830206 11/06/2019 07:14:55 11/06/2019 07:00:00
2830207 11/06/2019 07:39:02 11/06/2019 07:00:00
2830211 11/06/2019 07:54:52 11/06/2019 07:00:00
2830212 11/06/2019 08:00:50 11/06/2019 08:00:00
2830213 11/06/2019 08:00:53 11/06/2019 08:00:00
2830214 11/06/2019 08:01:06 11/06/2019 08:00:00


SELECT "Job No", "Submit_Date", "Submit_Time", 
       TO_DATE(TO_CHAR("Submit_Date", 'MM-DD-YYYY') || ' ' 
       || "Submit_Time", 'MM-DD-YYYY HH24:MI:SS') AS Datetime,
       TRUNC(TO_DATE(TO_CHAR("Submit_Date", 'MM-DD-YYYY') || ' ' 
       || "Submit_Time", 'MM-DD-YYYY HH24:MI:SS'), 'HH' ) AS truncDate
  FROM VW_SUBMITTEDJOBS 
 ORDER BY "Submit_Date" DESC;

I want to achieve something of this sort...

COUNT("Job No") or (*) 
 FROM VW_SUBMITTEDJOBS 
WHERE TruncDate BETWEEN 11/06/2019 00:00:00 AND 11/06/2019 01:00:00

Im expecting 4 as the answer as per the output above, then

11/06/2019 01:00:00 AND 11/06/2019 02:00:00 to show 4 ,

11/06/2019 02:00:00 AND 11/06/2019 03:00:00 to show 1

and so forth

Answer 1

You can truncate by HH24 argument :

SELECT TRUNC(Submit_Date,'HH24') as Submit_Date, COUNT(*) as "Count"
  FROM VW_SUBMITTEDJOBS
 GROUP BY TRUNC(Submit_Date,'HH24') 
 ORDER BY Submit_Date DESC;

Demo

or explicit formatting by to_char() after applying trunc() :

SELECT TO_CHAR(TRUNC(Submit_Date,'HH24'),'YYYY-MM-DD HH24') as "Submit Date", 
       COUNT(*) as "Count"
  FROM VW_SUBMITTEDJOBS
 GROUP BY TO_CHAR(TRUNC(Submit_Date,'HH24'),'YYYY-MM-DD HH24') 
 ORDER BY "Submit Date" DESC;

Answer 2

You can directly use the Submit_Date and Submit_Time in the WHERE clause as follows:

SELECT
    DATETIME,
    COUNT(1) AS "NUMBER OF JOBS"
FROM
    (
        SELECT
            "Job No",
            TRUNC(TO_DATE(TO_CHAR("Submit_Date", 'MM-DD-YYYY')
                          || ' '
                          || "Submit_Time", 'MM-DD-YYYY HH24:MI:SS'), 'HH') AS DATETIME
        FROM
            VW_SUBMITTEDJOBS
        WHERE
            TO_DATE(TO_CHAR("Submit_Date", 'MM-DD-YYYY')
                    || ' '
                    || "Submit_Time", 'MM-DD-YYYY HH24:MI:SS') 
                        BETWEEN '11/06/2019 00:00:00' AND '11/06/2019 01:00:00'
    )
GROUP BY
    TRUNC(DATETIME, 'HH');

Answer 3

You can truncate the datetime column in your where clause. For example to get all the jobs started between 1 AM and 2 AM:

with vw_submittedjobs(jobno, datetime) AS
(
SELECT 2830161, TO_DATE('11/06/2019 00:04:21','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830162, TO_DATE('11/06/2019 00:04:38','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830163, TO_DATE('11/06/2019 00:05:57','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830164, TO_DATE('11/06/2019 00:06:18','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830168, TO_DATE('11/06/2019 01:29:03','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830169, TO_DATE('11/06/2019 01:29:39','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830170, TO_DATE('11/06/2019 01:30:07','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830175, TO_DATE('11/06/2019 01:37:33','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830186, TO_DATE('11/06/2019 02:52:04','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830193, TO_DATE('11/06/2019 03:22:02','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830194, TO_DATE('11/06/2019 03:22:40','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830199, TO_DATE('11/06/2019 03:45:08','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830201, TO_DATE('11/06/2019 04:21:09','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830202, TO_DATE('11/06/2019 04:21:25','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830203, TO_DATE('11/06/2019 04:47:30','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830204, TO_DATE('11/06/2019 04:47:48','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830205, TO_DATE('11/06/2019 07:13:43','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830206, TO_DATE('11/06/2019 07:14:55','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830207, TO_DATE('11/06/2019 07:39:02','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830211, TO_DATE('11/06/2019 07:54:52','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830212, TO_DATE('11/06/2019 08:00:50','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830213, TO_DATE('11/06/2019 08:00:53','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2830214, TO_DATE('11/06/2019 08:01:06','MM/DD/YYYY HH24:MI:SS') FROM DUAL 
)
SELECT 
  COUNT(jobno) job_count
  FROM vw_submittedjobs
  WHERE TO_CHAR(TRUNC(datetime,'HH24'),'MM/DD/YYYY HH24:MI:SS') = '11/06/2019 01:00:00'