How to get all the records using CTE

Albert Chao

CREATE TABLE test_tab (
    col_name  VARCHAR2(20),
    log_time  TIMESTAMP(6),
    status    VARCHAR2(20)
);

INSERT INTO test_tab VALUES('Engineering','08-06-22 08:09:16.366000000 PM','UP');
INSERT INTO test_tab VALUES('Engineering','09-06-22 08:28:16.366000000 PM','UP');
INSERT INTO test_tab VALUES('Engineering','09-06-22 08:13:16.366000000 PM','DOWN');
INSERT INTO test_tab VALUES('Commerce','07-06-22 4:59:16.366000000 PM','DOWN');
INSERT INTO test_tab VALUES('Commerce','07-06-22 6:34:16.366000000 PM','UP');
INSERT INTO test_tab VALUES('Commerce','07-06-22 6:49:16.366000000 PM','DOWN');
INSERT INTO test_tab VALUES('Commerce','01-06-22 2:15:16.366000000 PM','UP');
INSERT INTO test_tab VALUES('Commerce','07-06-22 07:04:16.366000000 PM','UP');
COMMIT;

Tool used: Oracle Developer(18c)

I have one table test_tab in which there are multiple col_name values like 'Engineering', 'Commerce' etc just for testing purpose I have given two column names. I need to find out the start log time and end log time for particular col_name

Start_time logic : For particular col_name say 'Commerce' I need to check the minimum log time whose status is DOWN.

End_time logic: For the same col_name say 'Commerce' I need to check the start_time derived from above logic and see which is just greater than that time in status UP. That would be my end_time.

My attempt (Working only for 'Commerce'):

WITH a AS(
SELECT col_name,MIN(log_time)start_time
FROM test_tab WHERE status = 'DOWN'
GROUP BY col_name
),
b AS(
SELECT col_name,log_time end_time
    FROM( SELECT col_name,log_time,
        dense_rank() over (order by log_time asc)rnk
        FROM test_tab WHERE status = 'UP')
        WHERE rnk = 2
)
SELECT a.col_name,a.start_time,b.end_time FROM a
JOIN b ON(a.col_name = b.col_name);

But the above solution is not giving the result for 'Engineering'. It is just fetching details only for 'Commerce'

Expected Output:

Col_name    start_time                          end_time
Commerce    07-06-22 4:59:16.366000000 PM     07-06-22 6:34:16.366000000 PM
Engineering 09-06-22 08:13:16.366000000 PM    09-06-22 08:28:16.366000000 PM

Frank Kulash

Hi, @Albert Chao

End_time logic: For the same col_name say 'Commerce' I need to check the start_time derived from above logic and see which is just greater than that time in status UP. 

Do you mean end_time should be the earliest log_time from rows with status='UP' and log_time later than start_time? Here's one way to do that:

WITH  got_start_time  AS
(
  SELECT  col_name, log_time, status
  ,	  MIN ( CASE
  	  	   WHEN status = 'DOWN'
		   THEN log_time
		 END
  	  	) OVER ( PARTITION BY col_name) AS start_time
  FROM  test_tab
)
SELECT   col_name
,	 start_time
,	 MIN ( CASE
		  WHEN status   = 'UP'
		  AND  log_time > start_time
		  THEN log_time
	   	END
	   )		AS end_time
FROM	 got_start_time
GROUP BY col_name, start_time
ORDER BY col_name
;

Alex Nuijten

When you include a partition clause for your analytic function, it should give you the desired effect:

 dense_rank() over (partition by col_name order by log_time asc)rnk

Frank Kulash

Hi, @Albert Chao

CREATE TABLE test_tab (

  col_name VARCHAR2(20),

  log_time TIMESTAMP(6),

  status  VARCHAR2(20)

);

INSERT INTO test_tab VALUES('Engineering','08-06-22 08:09:16.366000000 PM','UP');

Don't try to insert string values (such as '08-06-22 08:09:16.366000000 PM') into TIMESTAMP columns (such as col_time). It may not cause an error on your system today, and it may even produce the results you want, but it may not work tomorrow, or on any other system. Use TO_TIMESTAMP for TIMESTAMP values.

Frank Kulash

Hi, @Albert Chao

End_time logic: For the same col_name say 'Commerce' I need to check the start_time derived from above logic and see which is just greater than that time in status UP. 

Do you mean end_time should be the earliest log_time from rows with status='UP' and log_time later than start_time? Here's one way to do that:

WITH  got_start_time  AS
(
  SELECT  col_name, log_time, status
  ,	  MIN ( CASE
  	  	   WHEN status = 'DOWN'
		   THEN log_time
		 END
  	  	) OVER ( PARTITION BY col_name) AS start_time
  FROM  test_tab
)
SELECT   col_name
,	 start_time
,	 MIN ( CASE
		  WHEN status   = 'UP'
		  AND  log_time > start_time
		  THEN log_time
	   	END
	   )		AS end_time
FROM	 got_start_time
GROUP BY col_name, start_time
ORDER BY col_name
;

mathguy

You didn't specify what to do if there may be more than one row with the same column_name and same log_time - especially if they have different status. In my solution below, the result in that case is indeterminate. (Perhaps "that case" is not possible - for example if you have a unique constraint on the combination of two columns.)

You didn't specify the required output if for a given column_name the status is never 'DOWN' (assuming that's possible). The query below will not show those column names at all in the output.

You didn't specify the required output if for a given column_name there is no 'UP' row after the first 'DOWN' row. In those cases, my query will show start_time as you specified, and NULL in the end_time.

You didn't specify if status can be anything other than 'UP' or 'DOWN', in particular if it may be NULL. In my query I assume the status can be anything (including NULL) - this just require a little bit of care in writing the query.

You didn't tell us your Oracle version; in versions 12.1 and later, you can use match_recognize as shown below.

select col_name, start_time, end_time
from   test_tab
match_recognize(
  partition by col_name
  order     by log_time
  measures  down.log_time as start_time,
            up.log_time   as end_time
  pattern   ( down not_up* up{0,1} )
  define    down   as status = 'DOWN' and match_number() = 1,
            not_up as lnnvl(status = 'UP'),
            up     as status = 'UP'
);

COL_NAME             START_TIME                     END_TIME                      
-------------------- ------------------------------ ------------------------------
Commerce             07-06-22 04:59:16.366000000 PM 07-06-22 06:34:16.366000000 PM
Engineering          09-06-22 08:13:16.366000000 PM 09-06-22 08:28:16.366000000 PM

Solomon Yakobson

Match recognize solution:

select  *
  from  test_tab
  match_recognize(
                  partition by col_name
                  order by log_time
                  measures
                    first(down.log_time) start_time,
                    last(up.log_time)  end_time
                  pattern(up* down+ up any_status*)
                  define up   as status = 'UP',
                         down as status = 'DOWN'
                 )
/

COL_NAME             START_TIME                     END_TIME
-------------------- ------------------------------ ------------------------------
Commerce             07-06-22 04:59:16.366000 pm    07-06-22 06:34:16.366000 pm
Engineering          09-06-22 08:13:16.366000 pm    09-06-22 08:28:16.366000 pm

SQL>

SY.

ora_1978

drop table test_tab;

CREATE TABLE test_tab (

  col_name VARCHAR2(20),

  log_time TIMESTAMP,

  status  VARCHAR2(20)

);

select sysdate from dual;

INSERT INTO test_tab VALUES('Engineering','08-JUN-22 08:09:16.366000000 PM','UP');

INSERT INTO test_tab VALUES('Engineering','09-JUN-22 08:28:16.366000000 PM','UP');

INSERT INTO test_tab VALUES('Engineering','09-JUN-22 08:13:16.366000000 PM','DOWN');

INSERT INTO test_tab VALUES('Commerce','07-JUN-22 4:59:16.366000000 PM','DOWN');

INSERT INTO test_tab VALUES('Commerce','07-JUN-22 6:34:16.366000000 PM','UP');

INSERT INTO test_tab VALUES('Commerce','07-JUN-22 6:49:16.366000000 PM','DOWN');

INSERT INTO test_tab VALUES('Commerce','01-JUN-22 2:15:16.366000000 PM','UP');

INSERT INTO test_tab VALUES('Commerce','07-JUN-22 07:04:16.366000000 PM','UP');

COMMIT;

select tbl.col_name, tbl.status_1,tbl.start_time,tbl.end_time, tbl.status_2 from 

(

SELECT a.col_name, a.log_time_1 start_time , b.LOG_TIME_2 end_time, a.status_1,ROW_NUMBER() OVER (partition by a.col_name ORDER BY b.log_time_2 desc) rn,

b.status_2 FROM 

(SELECT ROWNUM rn,COL_NAME,min(LOG_TIME) over(partition by col_name ) log_time_1 ,STATUS status_1 FROM test_tab where status ='DOWN') A,

(SELECT ROWNUM rn, COL_NAME,LOG_TIME LOG_TIME_2 ,STATUS status_2 FROM test_tab where status ='UP' ) b

WHERE b.rn - a.rn = 1  ) tbl where tbl.rn = 1;