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