Home » SQL & PL/SQL » SQL & PL/SQL » record count between one hour (Oracle 19c)
record count between one hour [message #685544] Fri, 04 February 2022 05:25 Go to next message
bkbora
Messages: 21
Registered: April 2020
Junior Member
Hi,
I have 2 columns rcv_date in format DD-MON-YY e.g 16-JAN-2022 and rcv_time in format HH:MM:SS e.g 23:26:29.89.
How can I get the no of records processed in each hour for the rcv_date.
Any help is much more appreciated.
.
--moderator edit: moved to a more appropriate forum

[Updated on: Sat, 05 February 2022 02:22] by Moderator

Report message to a moderator

Re: record count between one hour [message #685552 is a reply to message #685544] Sat, 05 February 2022 02:27 Go to previous messageGo to next message
John Watson
Messages: 8718
Registered: January 2010
Location: Global Village
Senior Member
One approach would be to create a virtual column of data type TIMESTAMP populated from your two existing columns, and GROUP BY it.

ps - I wish you would not say "record" when you mean "row".
Re: record count between one hour [message #685553 is a reply to message #685544] Sat, 05 February 2022 09:11 Go to previous messageGo to next message
EdStevens
Messages: 1375
Registered: September 2013
Senior Member
Quote:
I have 2 columns rcv_date in format DD-MON-YY e.g 16-JAN-2022 and rcv_time in format HH:MM:SS e.g 23:26:29.89.
If the example you give is correct, Your "time" is not in format hh:mm:ss. That format only goes down to seconds, but your example goes down to hundredths of seconds.

And the format 'HH' is specifying a 12-hour clock, so we can't tell if it is AM or PM.


And even beyond that, your data model is fundamentally flawed. Your rcv_date and rcv_time columns should be a single column of data tyhpe TIMESTAMP or DATE. It is a huge mistake to try to keep 'date' and 'time' components in separate columns. Correct your data model, and your solution becomes trivial.
Re: record count between one hour [message #685555 is a reply to message #685544] Sun, 06 February 2022 06:21 Go to previous message
Solomon Yakobson
Messages: 3151
Registered: January 2010
Location: Connecticut, USA
Senior Member
bkbora wrote on Fri, 04 February 2022 06:25
Hi,
I have 2 columns rcv_date in format DD-MON-YY e.g 16-JAN-2022 and rcv_time in format HH:MM:SS e.g 23:26:29.89.
How can I get the no of records processed in each hour for the rcv_date.
WITH DRIVER AS (
                SELECT  LPAD(LEVEL - 1,2,'0') RCV_HOUR
                  FROM  DUAL
                  CONNECT BY LEVEL <= 24
               )
SELECT  T.RCV_DATE,
        D.RCV_HOUR RCV_HOUR,
        COUNT(
              CASE SUBSTR(T.RCV_TIME,1,2)
                WHEN D.RCV_HOUR THEN 1
              END
             ) CNT
  FROM  YOUR_TABLE T,
        DRIVER D
  GROUP BY T.RCV_DATE,
           D.RCV_HOUR
  ORDER BY T.RCV_DATE,
           D.RCV_HOUR
/
Or

SELECT  T.RCV_DATE,
        L.RCV_HOUR RCV_HOUR,
        COUNT(L.MATCH) CNT
  FROM  YOUR_TABLE T,
        LATERAL(
                SELECT  LPAD(LEVEL - 1,2,'0') RCV_HOUR,
                        CASE LPAD(LEVEL - 1,2,'0')
                          WHEN SUBSTR(T.RCV_TIME,1,2) THEN 1
                        END MATCH
                  FROM  DUAL
                  CONNECT BY LEVEL <= 24
               ) L
  GROUP BY T.RCV_DATE,
           L.RCV_HOUR
  ORDER BY T.RCV_DATE,
           L.RCV_HOUR
/
SY.

[Updated on: Sun, 06 February 2022 06:47]

Report message to a moderator

Previous Topic: Oracle case INSERT statement
Next Topic: query
Goto Forum:
  


Current Time: Thu Jun 30 14:24:53 CDT 2022