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 next 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
icon5.gif  Re: Finding Date Pattern using LEAD [message #678413 is a reply to message #678412] Sun, 01 December 2019 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 66719
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is an inconsistency in your specification between "For each operator" and "for 100 operator id desired output" which shows several rows for a single operator. This means you don't want the result for "each operator" but "each operator and <something else>".
What is the "<something else>"? "OPERATOR_NM and START_CASE_ID"?
What is the primary key of your table?

[Updated on: Sun, 01 December 2019 03:14]

Report message to a moderator

icon4.gif  Re: Finding Date Pattern using LEAD [message #678414 is a reply to message #678413] Sun, 01 December 2019 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 66719
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also for "100 ABC C-1055828", "STOP" is before "START" Question

SQL> select * from TEST_START_STOP order by OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_DT;
OPERATOR_ID  OPERATOR_NM  START_CASE_ID BREAK_CD     BREAK_DT
------------ ------------ ------------- ------------ -----------------------
100          ABC          C-1055828     STOP         14/11/2019 16:25:26.315
100          ABC          C-1055828     START        14/11/2019 16:26:33.137
100          ABC          C-283489      START        14/11/2019 10:58:13.235
100          ABC          C-283489      STOP         14/11/2019 10:59:15.571
100          ABC          C-318234      STOP         14/11/2019 10:50:17.064
100          ABC          C-318236      STOP         14/11/2019 10:29:12.650
100          ABC          C-323079      START        14/11/2019 10:27:04.544
100          ABC          C-323079      START        14/11/2019 10:42:54.057
100          ABC          C-323079      START        14/11/2019 16:24:51.838
100          ABC          C-323079      STOP         14/11/2019 16:26:36.031
100          ABC          C-331406      STOP         14/11/2019 10:48:26.815
100          ABC          C-332167      STOP         14/11/2019 10:28:15.655
100          ABC          C-540942      START        14/11/2019 10:30:45.691
200          PQR          C-922235      START        14/11/2019 11:03:26.043
200          PQR          C-922235      STOP         14/11/2019 11:04:45.643
200          PQR          C-922235      START        14/11/2019 11:11:12.622
200          PQR          C-922235      STOP         14/11/2019 11:12:54.661
200          PQR          C-922235      START        14/11/2019 11:15:25.391
300          XYZ          C-1065847     START        14/11/2019 13:46:01.795
300          XYZ          C-977013      STOP         14/11/2019 13:46:24.160

[Updated on: Sun, 01 December 2019 03:18]

Report message to a moderator

Re: Finding Date Pattern using LEAD [message #678415 is a reply to message #678413] Sun, 01 December 2019 03:24 Go to previous messageGo to next message
samiran_cts
Messages: 49
Registered: January 2012
Member
operator_id = 100 (break_dt asc order) I'm looking for the output as follows

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

In this table there is no PK.
Re: Finding Date Pattern using LEAD [message #678416 is a reply to message #678415] Sun, 01 December 2019 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 66719
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your output is not correct:
11/14/2019 10:29:12.650025 AM C-540942
At this timestamp the case_id is C-318236 not C-540942, and the next row with case_id C-540942 is with flag START not STOP as it should be for and end_case_id as you speficied.

SQL> with
  2    data as (
  3      select OPERATOR_ID, OPERATOR_NM,
  4             case
  5               when BREAK_CD = 'START' and
  6                    lag(break_cd,1,'X') over (partition by OPERATOR_ID, OPERATOR_NM order by BREAK_DT) != 'START'
  7                 then START_CASE_ID
  8               when BREAK_CD = 'STOP' and
  9                    lead(break_cd,1,'X') over (partition by OPERATOR_ID, OPERATOR_NM order by BREAK_DT) != 'STOP'
 10                 then START_CASE_ID
 11             end START_CASE_ID,
 12             case
 13               when BREAK_CD = 'START' and
 14                    lag(break_cd,1,'X') over (partition by OPERATOR_ID, OPERATOR_NM order by BREAK_DT) != 'START'
 15                 then 'START'
 16               when BREAK_CD = 'STOP' and
 17                    lead(break_cd,1,'X') over (partition by OPERATOR_ID, OPERATOR_NM order by BREAK_DT) != 'STOP'
 18                 then 'STOP'
 19             end BREAK_CD,
 20             case
 21               when BREAK_CD = 'START' and
 22                    lag(break_cd,1,'X') over (partition by OPERATOR_ID, OPERATOR_NM order by BREAK_DT) != 'START'
 23                 then BREAK_DT
 24               when BREAK_CD = 'STOP' and
 25                    lead(break_cd,1,'X') over (partition by OPERATOR_ID, OPERATOR_NM order by BREAK_DT) != 'STOP'
 26                 then BREAK_DT
 27             end BREAK_DT
 28      from TEST_START_STOP
 29    ),
 30    compute as (
 31      select OPERATOR_ID, OPERATOR_NM, START_CASE_ID, BREAK_CD, BREAK_DT start_date,
 32             decode(break_cd, 'START', lead(BREAK_DT)
 33               over (partition by OPERATOR_ID, OPERATOR_NM order by BREAK_DT)) stop_date,
 34             decode(break_cd, 'START', lead(START_CASE_ID)
 35               over (partition by OPERATOR_ID, OPERATOR_NM order by BREAK_DT)) end_case_id
 36      from data
 37      where break_cd is not null
 38    )
 39  select OPERATOR_ID, OPERATOR_NM, START_CASE_ID, start_date, stop_date, end_case_id
 40  from compute
 41  where break_cd = 'START'
 42  order by OPERATOR_ID, OPERATOR_NM, start_date;
OPERATOR_ID  OPERATOR_NM  START_CASE_ID START_DATE              STOP_DATE               END_CASE_ID
------------ ------------ ------------- ----------------------- ----------------------- -------------
100          ABC          C-323079      14/11/2019 10:27:04.544 14/11/2019 10:29:12.650 C-318236
100          ABC          C-540942      14/11/2019 10:30:45.691 14/11/2019 10:50:17.064 C-318234
100          ABC          C-283489      14/11/2019 10:58:13.235 14/11/2019 10:59:15.571 C-283489
100          ABC          C-323079      14/11/2019 16:24:51.838 14/11/2019 16:25:26.315 C-1055828
100          ABC          C-1055828     14/11/2019 16:26:33.137 14/11/2019 16:26:36.031 C-323079
200          PQR          C-922235      14/11/2019 11:03:26.043 14/11/2019 11:04:45.643 C-922235
200          PQR          C-922235      14/11/2019 11:11:12.622 14/11/2019 11:12:54.661 C-922235
200          PQR          C-922235      14/11/2019 11:15:25.391
300          XYZ          C-1065847     14/11/2019 13:46:01.795 14/11/2019 13:46:24.160 C-977013

9 rows selected.
Re: Finding Date Pattern using LEAD [message #678417 is a reply to message #678412] Sun, 01 December 2019 09:38 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2899
Registered: January 2010
Location: Connecticut, USA
Senior Member
WITH T1 AS (
            SELECT  T.*,
                    CASE LAG(BREAK_CD) OVER(PARTITION BY OPERATOR_ID ORDER BY BREAK_DT)
                      WHEN BREAK_CD THEN 0
                      ELSE 1
                    END START_OF_GROUP
              FROM  TEST_START_STOP T
           ),
     T2 AS (
            SELECT  T1.*,
                    SUM(
                        CASE BREAK_CD
                          WHEN 'START' THEN START_OF_GROUP
                        END
                       ) OVER(PARTITION BY OPERATOR_ID ORDER BY BREAK_DT) GRP
              FROM  T1
           )
SELECT  OPERATOR_ID,
        OPERATOR_NM,
        MIN(START_CASE_ID) KEEP(DENSE_RANK FIRST ORDER BY BREAK_DT) START_CASE_ID,
        'Start' START_FLG,
        MIN(BREAK_DT) START_DATE,
        'Stop' STOP_FLG,
        MAX(BREAK_DT) STOP_DATE,
        MAX(START_CASE_ID) KEEP(DENSE_RANK LAST ORDER BY BREAK_DT) END_CASE_ID
  FROM  T2
  GROUP BY OPERATOR_ID,
           OPERATOR_NM,
           GRP
  ORDER BY OPERATOR_ID,
           GRP
/

OPERATOR_ID OPERATOR_NM START_CASE_ID START START_DATE                 STOP STOP_DATE                  END_CASE_ID
----------- ----------- ------------- ----- -------------------------- ---- -------------------------- -----------
100         ABC         C-323079      Start 11/14/2019 10:27:04.544186 Stop 11/14/2019 10:29:12.650025 C-318236
100         ABC         C-540942      Start 11/14/2019 10:30:45.691365 Stop 11/14/2019 10:50:17.064311 C-318234
100         ABC         C-283489      Start 11/14/2019 10:58:13.235323 Stop 11/14/2019 10:59:15.571036 C-283489
100         ABC         C-323079      Start 11/14/2019 16:24:51.838605 Stop 11/14/2019 16:25:26.315507 C-1055828
100         ABC         C-1055828     Start 11/14/2019 16:26:33.137817 Stop 11/14/2019 16:26:36.031964 C-323079
200         PQR         C-922235      Start 11/14/2019 11:03:26.043662 Stop 11/14/2019 11:04:45.643906 C-922235
200         PQR         C-922235      Start 11/14/2019 11:11:12.622049 Stop 11/14/2019 11:12:54.661742 C-922235
200         PQR         C-922235      Start 11/14/2019 11:15:25.391536 Stop 11/14/2019 11:15:25.391536 C-922235
300         XYZ         C-1065847     Start 11/14/2019 13:46:01.795102 Stop 11/14/2019 13:46:24.160058 C-977013

9 rows selected.

SQL> 
SY.
Re: Finding Date Pattern using LEAD [message #678418 is a reply to message #678417] Sun, 01 December 2019 12:12 Go to previous messageGo to next message
Michel Cadot
Messages: 66719
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Solomon,

The last START (and row) for id=200 has no STOP.

Re: Finding Date Pattern using LEAD [message #678419 is a reply to message #678418] Sun, 01 December 2019 13:38 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2899
Registered: January 2010
Location: Connecticut, USA
Senior Member
Not an issue:

WITH T1 AS (
            SELECT  T.*,
                    CASE LAG(BREAK_CD) OVER(PARTITION BY OPERATOR_ID ORDER BY BREAK_DT)
                      WHEN BREAK_CD THEN 0
                      ELSE 1
                    END START_OF_GROUP
              FROM  TEST_START_STOP T
           ),
     T2 AS (
            SELECT  T1.*,
                    SUM(
                        CASE BREAK_CD
                          WHEN 'START' THEN START_OF_GROUP
                        END
                       ) OVER(PARTITION BY OPERATOR_ID ORDER BY BREAK_DT) GRP
              FROM  T1
           )
SELECT  OPERATOR_ID,
        OPERATOR_NM,
        MIN(START_CASE_ID) KEEP(DENSE_RANK FIRST ORDER BY BREAK_DT) START_CASE_ID,
        'Start' START_FLG,
        MIN(BREAK_DT) START_DATE,
        'Stop' STOP_FLG,
        MAX(CASE BREAK_CD WHEN 'STOP' THEN BREAK_DT END) STOP_DATE,
        MAX(CASE BREAK_CD WHEN 'STOP' THEN START_CASE_ID END) KEEP(DENSE_RANK LAST ORDER BY BREAK_DT) END_CASE_ID
  FROM  T2
  GROUP BY OPERATOR_ID,
           OPERATOR_NM,
           GRP
  ORDER BY OPERATOR_ID,
           GRP
/

OPERATOR_ID OPERATOR_NM START_CASE_ID START START_DATE                 STOP STOP_DATE                  END_CASE_ID
----------- ----------- ------------- ----- -------------------------- ---- -------------------------- -----------
100         ABC         C-323079      Start 11/14/2019 10:27:04.544186 Stop 11/14/2019 10:29:12.650025 C-318236
100         ABC         C-540942      Start 11/14/2019 10:30:45.691365 Stop 11/14/2019 10:50:17.064311 C-318234
100         ABC         C-283489      Start 11/14/2019 10:58:13.235323 Stop 11/14/2019 10:59:15.571036 C-283489
100         ABC         C-323079      Start 11/14/2019 16:24:51.838605 Stop 11/14/2019 16:25:26.315507 C-1055828
100         ABC         C-1055828     Start 11/14/2019 16:26:33.137817 Stop 11/14/2019 16:26:36.031964 C-323079
200         PQR         C-922235      Start 11/14/2019 11:03:26.043662 Stop 11/14/2019 11:04:45.643906 C-922235
200         PQR         C-922235      Start 11/14/2019 11:11:12.622049 Stop 11/14/2019 11:12:54.661742 C-922235
200         PQR         C-922235      Start 11/14/2019 11:15:25.391536 Stop
300         XYZ         C-1065847     Start 11/14/2019 13:46:01.795102 Stop 11/14/2019 13:46:24.160058 C-977013

9 rows selected.

SQL> 
SY.
Re: Finding Date Pattern using LEAD [message #678420 is a reply to message #678416] Sun, 01 December 2019 13:51 Go to previous message
samiran_cts
Messages: 49
Registered: January 2012
Member
Thanks Solomon and Michel. It will solve my problem.

As I have to create report and make the time difference between each start and stop, so in case of Solomon's query it will come 0 or in Michel's query the stop is NULL, so both cases I can Identify that operator makes some mistake.

@Michel - it is correct that caseid may differ from start and stop. It is valid scenario for my case.

Again Thanks a lot both of you.
Previous Topic: date format
Next Topic: Want to know insert into
Goto Forum:
  


Current Time: Mon Dec 09 19:39:19 CST 2019