Oracle sql query to group two sets of dates if sequencials

Tags: , , ,



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

Answer

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:

enter image description here

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 –



Source: stackoverflow