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 |
|
samiran_cts
Messages: 52 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
|
|
|
Re: Finding Date Pattern using LEAD [message #678413 is a reply to message #678412] |
Sun, 01 December 2019 03:07 |
|
Michel Cadot
Messages: 68658 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
|
|
|
|
|
Re: Finding Date Pattern using LEAD [message #678416 is a reply to message #678415] |
Sun, 01 December 2019 04:23 |
|
Michel Cadot
Messages: 68658 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 |
Solomon Yakobson
Messages: 3275 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 #678419 is a reply to message #678418] |
Sun, 01 December 2019 13:38 |
Solomon Yakobson
Messages: 3275 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 |
|
samiran_cts
Messages: 52 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.
|
|
|
Goto Forum:
Current Time: Fri Jun 14 09:41:56 CDT 2024
|