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.