OraFAQ Forum: SQL & PL/SQL » record count between one hour

Home » SQL & PL/SQL » SQL & PL/SQL » record count between one hour (Oracle 19c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

record count between one hour [message #685544]

Fri, 04 February 2022 05:25

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

John Watson
Messages: 8805
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".

Report message to a moderator

Re: record count between one hour [message #685553 is a reply to message #685544]

Sat, 05 February 2022 09:11

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.

Report message to a moderator

Re: record count between one hour [message #685555 is a reply to message #685544]

Sun, 06 February 2022 06:21

Solomon Yakobson
Messages: 3214
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
/

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 30 22:38:07 CDT 2023