Home » SQL & PL/SQL » SQL & PL/SQL » Finding Date Pattern using LEAD (Oracle 12c)
Finding Date Pattern using LEAD [message #678412] Sun, 01 December 2019 02:45 Go to previous message
samiran_cts
Messages: 49
Registered: January 2012
Member
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
 
Read Message
Read Message icon5.gif
Read Message icon4.gif
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: date format
Next Topic: Want to know insert into
Goto Forum:
  


Current Time: Mon Dec 09 20:20:15 CST 2019