CODEWITHSUNDEEP
sqloracle-databasedate
onur
onur

Reputation: 6365

How can I group by date interval in the two columns?

I have table like this:

enter image description here

enter image description here

I need to find date value of file column and group by date-file date values interval . I tried like this:

select date,regexp_substr(FILE, '[[:DIGIT:]]{12}') FILEDATE from table

It's show date and file date. How can I group by date-file date interval like this?:

enter image description here

Upvotes: 1

Views: 106

Answers (1)

MT0
MT0

Reputation: 167832

If the values in the "DATE" and "FILE" columns are going to have the same times then you can use:

SELECT   "DATE" - TO_DATE( REGEXP_SUBSTR( "FILE", '\d{14}', 1, 1 ), 'YYYYMMDDHH24MISS' ) AS "INTERVAL"
         COUNT(1) AS "COUNT"
FROM     TABLE_NAME
GROUP BY "DATE" - TO_DATE( REGEXP_SUBSTR( "FILE", '\d{14}', 1, 1 ), 'YYYYMMDDHH24MISS' );

If they will have different times then wrap the "INTERVAL" and GROUP BY expressions in a CEIL() (or FLOOR()) function - alternately you can TRUNC() the dates before subtracting; like this:

SELECT   TRUNC( "DATE" ) - TRUNC( TO_DATE( REGEXP_SUBSTR( "FILE", '\d{14}', 1, 1 ), 'YYYYMMDDHH24MISS' ) ) AS "INTERVAL"
         COUNT(1) AS "COUNT"
FROM     TABLE_NAME
GROUP BY TRUNC( "DATE" ) - TRUNC( TO_DATE( REGEXP_SUBSTR( "FILE", '\d{14}', 1, 1 ), 'YYYYMMDDHH24MISS' ) );

Upvotes: 1

Related Questions