Oracle sql query to group two sets of dates if sequencials

Question

I have the next table:

EMPLOYEE	CODE	START_DATE	END_DATE
02097368	F7H3	09/07/2018	20/10/2018
02097368	F7H3	21/10/2018	05/01/2019
02097368	F7H3	06/01/2019	12/01/2019
02097368	F7H3	13/01/2019	02/02/2019
02097368	F7H3	03/02/2019	13/04/2019
02097368	F7S3	14/04/2019	04/01/2020
02097368	F7S3	05/01/2020	24/03/2020
02097368	F7S3	31/01/2021

I would like to group the data by employee, code and sequencial date(end_date to the next start_date if its 1 day later)

DESIRED RESULT:

EMPLOYEE	CODE	START_DATE	END_DATE
02097368	F7H3	09/07/2018	13/04/2019
02097368	F7S3	14/04/2019	24/03/2020
02097368	F7S3	31/01/2021

Im trying this but im not getting the desired result

SELECT EMPLOYEE,
       CODE,
       MIN (START_DATE) AS START_DATE,
       MAX (END_DATE)
          KEEP (DENSE_RANK FIRST ORDER BY END_DATE DESC NULLS FIRST)
          AS END_DATE
  FROM (SELECT T.*,
               ROW_NUMBER ()
                  OVER (PARTITION BY EMPLOYEE ORDER BY START_DATE)
                  AS seqnum_i,
               ROW_NUMBER ()
               OVER (PARTITION BY EMPLOYEE, CODE ORDER BY START_DATE)
                  AS seqnum_ir
          FROM CODE_HIST T
         WHERE EMPLOYEE= '02097368') T
GROUP BY ID_EMPLEADO, (seqnum_i - seqnum_ir), CODE;

ACTUAL RESULT:

EMPLOYEE	CODE	START_DATE	END_DATE
02097368	F7H3	09/07/2018	13/04/2019
02097368	F7S3	14/04/2019

So you mean "if ROW N has a start date that is one day after ROW N-1, treat the two rows as one/as if the date range was continuous? — Caius Jard
– Caius Jard, Commented May 27, 2021 at 20:10
exactly, starting a new row only when the either the CODE is different or the date range isn´t continuous. — Alejandro Rodriguez
– Alejandro Rodriguez, Commented May 28, 2021 at 2:50

Scinana · Accepted Answer · 2021-05-27 22:35:30Z

1

I solved this with the following query though I know it's not the most elegant one. Do replace TABLE_1 for the actual table name or view that holds your data

When running the following, I retrieved the below results

SELECT EMPLOYEE, CODE, MIN(START_DATE), MAX(END_DATE) FROM 
(
     SELECT EMPLOYEE, CODE, START_DATE, END_DATE, CASE
     WHEN (TEMP1=1 OR TEMP2=1) THEN 'ONE_CLASS'
     ELSE 'OTHER' END  CLASS_X
     FROM            
                (SELECT EMPLOYEE, CODE, 
                START_DATE, END_DATE,
                X AS TEMP1, LEAD(X) OVER (ORDER BY START_DATE) AS TEMP2
                            FROM (
                            SELECT EMPLOYEE, CODE,  
                            LAG(END_DATE) OVER (ORDER BY START_DATE) AS PREV_PERIOD_END_DATE, 
                            START_DATE, END_DATE,
                            START_DATE - LAG(END_DATE) OVER (ORDER BY START_DATE) AS X
                            FROM TABLE_2
                            ORDER BY START_DATE) A
                            ) B
                            ) C
GROUP BY EMPLOYEE, CODE, CLASS_X
ORDER BY MIN(START_DATE)

Results:

Results in text form that I don't know how to format as a table:

EMPLOYEE CODE MIN(START_DATE) MAX(END_DATE) 02097368 F7H3 09-JUL-18 13-APR-19 02097368 F7S3 14-APR-19 24-MAR-20 02097368 F7S3 31-JAN-21 -

edited May 27, 2021 at 22:35

answered May 27, 2021 at 21:23

Scinana

4025 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dave Costa Over a year ago

That looks like it would produce 2 rows, not the 3 that the questioner is looking for.

Scinana Over a year ago

yes you are right! apologies, will amend this now

Collectives™ on Stack Overflow

Oracle sql query to group two sets of dates if sequencials

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related