I have a table CREATE TABLE TEST_START_STOP
(
OPERATOR_ID VARCHAR2(128 BYTE),
OPERATOR_NM VARCHAR2(128 BYTE),
START_CASE_ID VARCHAR2(255 BYTE),
BREAK_CD VARCHAR2(255 BYTE),
BREAK_DT TIMESTAMP(6)
)
Insert into TEST_START_STOP
(OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_CD, BREAK_DT)
Values
('100', 'ABC', 'C-323079', 'START', TO_TIMESTAMP('11/14/2019 10:27:04.544186 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TEST_START_STOP
(OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_CD, BREAK_DT)
Values
('100', 'ABC', 'C-332167', 'STOP', TO_TIMESTAMP('11/14/2019 10:28:15.655138 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TEST_START_STOP
(OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_CD, BREAK_DT)
Values
('100', 'ABC', 'C-318236', 'STOP', TO_TIMESTAMP('11/14/2019 10:29:12.650025 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TEST_START_STOP
(OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_CD, BREAK_DT)
Values
('100', 'ABC', 'C-540942', 'START', TO_TIMESTAMP('11/14/2019 10:30:45.691365 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TEST_START_STOP
(OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_CD, BREAK_DT)
Values
('100', 'ABC', 'C-323079', 'START', TO_TIMESTAMP('11/14/2019 10:42:54.057382 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TEST_START_STOP
(OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_CD, BREAK_DT)
Values
('100', 'ABC', 'C-331406', 'STOP', TO_TIMESTAMP('11/14/2019 10:48:26.815340 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TEST_START_STOP
(OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_CD, BREAK_DT)
Values
('100', 'ABC', 'C-318234', 'STOP', TO_TIMESTAMP('11/14/2019 10:50:17.064311 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TEST_START_STOP
(OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_CD, BREAK_DT)
Values
('100', 'ABC', 'C-283489', 'START', TO_TIMESTAMP('11/14/2019 10:58:13.235323 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TEST_START_STOP
(OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_CD, BREAK_DT)
Values
('100', 'ABC', 'C-283489', 'STOP', TO_TIMESTAMP('11/14/2019 10:59:15.571036 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TEST_START_STOP
(OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_CD, BREAK_DT)
Values
('100', 'ABC', 'C-323079', 'START', TO_TIMESTAMP('11/14/2019 4:24:51.838605 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TEST_START_STOP
(OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_CD, BREAK_DT)
Values
('100', 'ABC', 'C-1055828', 'STOP', TO_TIMESTAMP('11/14/2019 4:25:26.315507 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TEST_START_STOP
(OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_CD, BREAK_DT)
Values
('100', 'ABC', 'C-1055828', 'START', TO_TIMESTAMP('11/14/2019 4:26:33.137817 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TEST_START_STOP
(OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_CD, BREAK_DT)
Values
('100', 'ABC', 'C-323079', 'STOP', TO_TIMESTAMP('11/14/2019 4:26:36.031964 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TEST_START_STOP
(OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_CD, BREAK_DT)
Values
('200', 'PQR', 'C-922235', 'START', TO_TIMESTAMP('11/14/2019 11:03:26.043662 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TEST_START_STOP
(OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_CD, BREAK_DT)
Values
('200', 'PQR', 'C-922235', 'STOP', TO_TIMESTAMP('11/14/2019 11:04:45.643906 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TEST_START_STOP
(OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_CD, BREAK_DT)
Values
('200', 'PQR', 'C-922235', 'START', TO_TIMESTAMP('11/14/2019 11:11:12.622049 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TEST_START_STOP
(OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_CD, BREAK_DT)
Values
('200', 'PQR', 'C-922235', 'STOP', TO_TIMESTAMP('11/14/2019 11:12:54.661742 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TEST_START_STOP
(OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_CD, BREAK_DT)
Values
('200', 'PQR', 'C-922235', 'START', TO_TIMESTAMP('11/14/2019 11:15:25.391536 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TEST_START_STOP
(OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_CD, BREAK_DT)
Values
('300', 'XYZ', 'C-1065847', 'START', TO_TIMESTAMP('11/14/2019 1:46:01.795102 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TEST_START_STOP
(OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_CD, BREAK_DT)
Values
('300', 'XYZ', 'C-977013', 'STOP', TO_TIMESTAMP('11/14/2019 1:46:24.160058 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
COMMIT;
For each operator I am looking for start and stop time in one row. but for some operator if 2 start come consecutive then I have to consider first start time and discard the 2nd START time and if 2 or more STOP comes then I have to consider the last STOP time. I tried the following query, it is good if data is good like START and STOP consecutively like operator = '300'
select *
from (
select t1.OPERATOR_ID, t1.OPERATOR_NM, t1.BREAK_CD, t1.BREAK_DT, t1.START_CASE_ID,
lead (t1.BREAK_CD, 1, 0) over ( order by t1.BREAK_DT) next_break_cd,
lead (t1.BREAK_DT, 1) over ( order by t1.BREAK_DT) End_Break_dt,
lead (t1.START_CASE_ID, 1) over ( order by t1.BREAK_DT) End_CASE_ID
from RPTOWN.TEST_START_STOP t1
where t1.OPERATOR_ID ='300'
) where next_break_cd != '0'
for 100 operator id desired output
OPERATOR_ID OPERATOR_NM START_CASE_ID START_FLG START_Date STOP_FLG STOP_Date END_CASE_ID
100 ABC C-323079 Start 11/14/2019 10:27:04.544186 AM Stop 11/14/2019 10:29:12.650025 AM C-540942
100 ABC C-540942 Start 11/14/2019 10:30:45.691365 AM Stop 11/14/2019 10:50:17.064311 AM C-318234
100 ABC C-283489 Start 11/14/2019 10:58:13.235323 AM Stop 11/14/2019 10:59:15.571036 AM C-283489
|