How to group by value to identify min and max date, but in specific order?

Question

I tried to find something here but I didn't find my use-case. I hope you can assist me. At first my table which is available:

STATION_NUMBER	PART_NO	BOOK_DATE
11111	A	2021-08-01 6:00:00
11111	A	2021-08-01 6:05:00
11111	A	2021-08-01 6:07:00
11111	A	2021-08-01 6:08:00
11111	B	2021-08-01 7:10:00
11111	B	2021-08-01 7:13:00
11111	B	2021-08-01 7:15:00
11111	B	2021-08-01 7:25:00
11111	A	2021-08-01 8:10:00
11111	A	2021-08-01 8:12:00
11111	A	2021-08-01 8:16:00
11111	A	2021-08-01 8:19:00
22222	A	2021-08-01 6:00:00
22222	A	2021-08-01 6:05:00
22222	A	2021-08-01 6:07:00
22222	A	2021-08-01 6:08:00
22222	B	2021-08-01 7:10:00
22222	B	2021-08-01 7:13:00
22222	B	2021-08-01 7:15:00
22222	B	2021-08-01 7:25:00
22222	A	2021-08-01 8:10:00
22222	A	2021-08-01 8:12:00
22222	A	2021-08-01 8:16:00
22222	A	2021-08-01 8:19:00

Following result what I want to get:

STATION_NUMBER	PART_NO	START_BOOK_DATE	END_BOOK_DATE
11111	A	2021-08-01 6:00:00	2021-08-01 6:08:00
11111	B	2021-08-01 7:10:00	2021-08-01 7:25:00
11111	A	2021-08-01 8:10:00	2021-08-01 8:19:00
22222	A	2021-08-01 6:00:00	2021-08-01 6:08:00
22222	B	2021-08-01 7:10:00	2021-08-01 7:25:00
22222	A	2021-08-01 8:10:00	2021-08-01 8:19:00

I tried to solve it with this query but I didn't what I expect

SELECT PART_NO,
      STATION_NUMBER,
      GROUP_NUMBER,
      MIN(BOOK_DATE) START_BOOK_DATE,
      MAX(BOOK_DATE) END_BOOK_DATE
FROM(
    SELECT PART_NO,
           STATION_NUMBER,
           BOOK_DATE,
           IS_CHANGED,
           RANK() OVER (ORDER BY PART_NO,IS_CHANGED) GROUP_NUMBER
    FROM(
        SELECT PART_NO,
        STATION_NUMBER,
        BOOK_DATE,
        CASE 
            WHEN NOT LEAD(PART_NO, 1) OVER (ORDER BY BOOK_DATE) = PART_NO
            THEN ROWNUM
            ELSE 0
        END IS_CHANGED
        FROM PROD_DATA
        WHERE STATION_NUMBER in ('11111','22222')
        AND BOOK_DATE BETWEEN TO_TIMESTAMP('01.08.2021 05:00:00', 'DD.MM.YYYY HH24:MI:SS') and TO_TIMESTAMP('01.08.2021 12:00:00', 'DD.MM.YYYY HH24:MI:SS')
        ORDER BY BOOK_DATE
    )ORDER BY BOOK_DATE
) GROUP BY STATION_NUMBER, PART_NO, GROUP_NUMBER

I must group by STATION_NUMBER and PART_NUMBER but I need the first and last BOOK_DATE from chronologic perspective. PART_NUMBER and/or STATION_NUMBER change is the trigger for me to calculate the new line.

Answer 1

Your problem falls in the class of problems known as "gaps and islands problems" (if you want to research further, Google for that phrase).

In Oracle 11 and earlier, you can use analytic functions to get the desired result. The method is known as the "tabibitosan method" or "fixed differences method".

The key step comes first (in the subquery in the with clause below): compute the sequential number as grouped (partitioned) by station number only, and separately as partitioned by station and part number. The difference is constant in a sequence of consecutive rows where the part number is the same, and then that difference jumps to a different value when a new such sequence begins. Then you use that for grouping in the outer query.

with 
  prep as (
    select pd.*,
           row_number() over (partition by station_number order by book_date)
         - row_number() over (partition by station_number, part_no
                                  order by book_date) as grp
    from   prod_date pd
  )
select station_number, part_no, min(book_date) as start_book_date,
       max(book_date) as end_book_date
from   prep
group  by station_number, part_no, grp
order  by station_number, start_book_date
;

STATION_NUMBER PART_NO START_BOOK_DATE     END_BOOK_DATE      
-------------- ------- ------------------- -------------------
         11111 A       2021-08-01 06:00:00 2021-08-01 06:08:00
         11111 B       2021-08-01 07:10:00 2021-08-01 07:25:00
         11111 A       2021-08-01 08:10:00 2021-08-01 08:19:00
         22222 A       2021-08-01 06:00:00 2021-08-01 06:08:00
         22222 B       2021-08-01 07:10:00 2021-08-01 07:25:00
         22222 A       2021-08-01 08:10:00 2021-08-01 08:19:00