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

CodePudding user response：

From Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row processing:

SELECT *
FROM   test_tab
MATCH_RECOGNIZE(
  PARTITION BY col_name
  ORDER BY log_time
  MEASURES
    FIRST(down.log_time) AS start_time,
    FIRST(up.log_time) AS end_time
  PATTERN (^ other*? down  up)
  DEFINE
    down AS status = 'DOWN',
    up   AS status = 'UP'
)

Which, for the sample data, outputs:

COL_NAME	START_TIME	END_TIME
Commerce	07-JUN-22 16.59.16.366000	07-JUN-22 18.34.16.366000
Engineering	09-JUN-22 20.13.16.366000	09-JUN-22 20.28.16.366000

db<>fiddle here

CodePudding user response：

You can find the minimum "down" time using an analytic function for each col_name partition and then filter to only have the subsequent "up" times and find the minimum of those:

SELECT col_name,
       MIN(min_down) AS start_time,
       MIN(log_time) AS end_time
FROM   (
  SELECT t.*,
         MIN(CASE status WHEN 'DOWN' THEN log_time END)
           OVER (PARTITION BY col_name) AS min_down
  FROM   test_tab t
)
WHERE log_time >= min_down
AND   status = 'UP'
GROUP BY col_name

Which, for the sample data, outputs:

COL_NAME	START_TIME	END_TIME
Commerce	07-JUN-22 16.59.16.366000	07-JUN-22 18.34.16.366000
Engineering	09-JUN-22 20.13.16.366000	09-JUN-22 20.28.16.366000

db<>fiddle here