Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
CREATE TABLE cardboard
(
    id int,
    Cardboard_Number varchar2(100),
	date_Time TIMESTAMP(0),
	ProductionLine_Number int
);
INSERT INTO cardboard VALUES 
(2,'WDL-005943998-1', TIMESTAMP '2014-08-05 10:03:32', 1),
(4,'spL1ml82N4o',TIMESTAMP '2024-02-29 17:13:54', 1),
(5,'WDL-005943998-1',TIMESTAMP '2024-03-01 09:44:42', 1),
(6,'WDL-005943998-1',TIMESTAMP '2024-03-01 10:34:57', 1),
(7,'950024027237',TIMESTAMP '2024-03-01 10:44:57', 1),
(8,'950024027237',TIMESTAMP '2024-03-01 10:52:57', 1),
(9,'WDL-005943998-1',TIMESTAMP '2024-03-01 13:58:43', 2),
(10,'WDL-005943998-1',TIMESTAMP '2024-03-01 13:58:46', 2),
(11,'spL1ml82N4o',TIMESTAMP '2024-03-01 14:09:43', 2),
(12,'WDL-005943998-1',TIMESTAMP '2024-03-12 15:48:36', 2);
10 rows affected

CREATE TABLE Productions
(	
  PRODUCTION_NUMBER NUMBER, 
  POSNR NUMBER, 
  DATETIME  TIMESTAMP(0), 
  PROCESS_ACTIVE VARCHAR2(1),
  PRODUCTION_LINE NUMBER
);
BEGIN
INSERT INTO Productions(PRODUCTION_NUMBER, POSNR, DATETIME, PROCESS_ACTIVE, PRODUCTION_LINE)
  VALUES (461793, 1, TO_TIMESTAMP('2014-08-04 09:01:41', 'YYYY-MM-DD HH24:MI:SS'), '1', 1);
INSERT INTO Productions(PRODUCTION_NUMBER, POSNR, DATETIME, PROCESS_ACTIVE, PRODUCTION_LINE)
  VALUES (461793, 1, TO_TIMESTAMP('2014-08-04 11:01:41', 'YYYY-MM-DD HH24:MI:SS'), '0', 1);
INSERT INTO Productions(PRODUCTION_NUMBER, POSNR, DATETIME, PROCESS_ACTIVE, PRODUCTION_LINE)
  VALUES (461618, 2, TO_TIMESTAMP('2014-08-05 10:01:41', 'YYYY-MM-DD HH24:MI:SS'), '1', 1);
INSERT INTO Productions(PRODUCTION_NUMBER, POSNR, DATETIME, PROCESS_ACTIVE, PRODUCTION_LINE)
  VALUES (461619, 2, TO_TIMESTAMP('2014-08-05 10:02:46', 'YYYY-MM-DD HH24:MI:SS'), '1', 2);
INSERT INTO Productions(PRODUCTION_NUMBER, POSNR, DATETIME, PROCESS_ACTIVE, PRODUCTION_LINE)
  VALUES (461618, 2, TO_TIMESTAMP('2014-08-05 10:05:09', 'YYYY-MM-DD HH24:MI:SS'), '0', 1);
INSERT INTO Productions(PRODUCTION_NUMBER, POSNR, DATETIME, PROCESS_ACTIVE, PRODUCTION_LINE)
  VALUES (461619, 2, TO_TIMESTAMP('2014-08-05 10:07:46', 'YYYY-MM-DD HH24:MI:SS'), '0', 2);
INSERT INTO Productions(PRODUCTION_NUMBER, POSNR, DATETIME, PROCESS_ACTIVE, PRODUCTION_LINE)
  VALUES (461818, 1, TO_TIMESTAMP('2014-08-14 22:53:12', 'YYYY-MM-DD HH24:MI:SS'), '1', 1);
INSERT INTO Productions(PRODUCTION_NUMBER, POSNR, DATETIME, PROCESS_ACTIVE, PRODUCTION_LINE)
  VALUES (461818, 1, TO_TIMESTAMP('2014-08-14 23:25:30', 'YYYY-MM-DD HH24:MI:SS'), '0', 1);
END;
/
1 rows affected

WITH production_bounds (production_number, posnr, process_active, production_line, start_dt, end_dt) AS (
  SELECT PRODUCTION_NUMBER,
         POSNR,
         PROCESS_ACTIVE,
         PRODUCTION_LINE,
         DATETIME as start_dt,
         LEAD(DATETIME, 1, SYSTIMESTAMP) OVER (
           PARTITION BY production_line
           ORDER BY DATETIME ASC
         ) AS end_dt
  FROM   productions
)
SELECT p.production_number,
       p.posnr,
       p.process_active,
       p.production_line,
       p.start_dt,
       p.end_dt,
       c.id,
       c.cardboard_number
FROM   production_bounds p
       INNER JOIN cardboard c
       ON     p.production_line = c.productionline_number
          AND c.date_time BETWEEN p.start_dt AND p.end_dt


PRODUCTION_NUMBER
POSNR
PROCESS_ACTIVE
PRODUCTION_LINE
START_DT
END_DT
ID
CARDBOARD_NUMBER


461618
2
1
1
2014-08-05 10:01:41.
2014-08-05 10:05:09.000000
2
WDL-005943998-1

461818
1
0
1
2014-08-14 23:25:30.
2024-03-28 12:53:28.569815
4
spL1ml82N4o

461818
1
0
1
2014-08-14 23:25:30.
2024-03-28 12:53:28.569815
5
WDL-005943998-1

461818
1
0
1
2014-08-14 23:25:30.
2024-03-28 12:53:28.569815
6
WDL-005943998-1

461818
1
0
1
2014-08-14 23:25:30.
2024-03-28 12:53:28.569815
7
950024027237

461818
1
0
1
2014-08-14 23:25:30.
2024-03-28 12:53:28.569815
8
950024027237

461619
2
0
2
2014-08-05 10:07:46.
2024-03-28 12:53:28.569815
9
WDL-005943998-1

461619
2
0
2
2014-08-05 10:07:46.
2024-03-28 12:53:28.569815
10
WDL-005943998-1

461619
2
0
2
2014-08-05 10:07:46.
2024-03-28 12:53:28.569815
11
spL1ml82N4o

461619
2
0
2
2014-08-05 10:07:46.
2024-03-28 12:53:28.569815
12
WDL-005943998-1


WITH production_bounds (production_number, posnr, process_active, production_line, start_dt, end_dt) AS (
  SELECT PRODUCTION_NUMBER,
         POSNR,
         PROCESS_ACTIVE,
         PRODUCTION_LINE,
         DATETIME as start_dt,
         LEAD(DATETIME, 1, SYSTIMESTAMP) OVER (
           PARTITION BY production_line
           ORDER BY DATETIME ASC
         ) AS end_dt
  FROM   productions
)
SELECT p.production_number,
       p.posnr,
       p.process_active,
       p.production_line,
       p.start_dt,
       p.end_dt,
       c.id,
       c.cardboard_number
FROM   production_bounds p
       INNER JOIN cardboard c
       ON     p.production_line = c.productionline_number
          AND c.date_time BETWEEN p.start_dt AND p.end_dt
WHERE  cardboard_number = 'WDL-005943998-1'
AND    process_active = 1


PRODUCTION_NUMBER
POSNR
PROCESS_ACTIVE
PRODUCTION_LINE
START_DT
END_DT
ID
CARDBOARD_NUMBER


461618
2
1
1
2014-08-05 10:01:41.
2014-08-05 10:05:09.000000
2
WDL-005943998-1

PRODUCTION_NUMBER	POSNR	PROCESS_ACTIVE	PRODUCTION_LINE	START_DT	END_DT	ID	CARDBOARD_NUMBER
461618	2	1	1	2014-08-05 10:01:41.	2014-08-05 10:05:09.000000	2	WDL-005943998-1
461818	1	0	1	2014-08-14 23:25:30.	2024-03-28 12:53:28.569815	4	spL1ml82N4o
461818	1	0	1	2014-08-14 23:25:30.	2024-03-28 12:53:28.569815	5	WDL-005943998-1
461818	1	0	1	2014-08-14 23:25:30.	2024-03-28 12:53:28.569815	6	WDL-005943998-1
461818	1	0	1	2014-08-14 23:25:30.	2024-03-28 12:53:28.569815	7	950024027237
461818	1	0	1	2014-08-14 23:25:30.	2024-03-28 12:53:28.569815	8	950024027237
461619	2	0	2	2014-08-05 10:07:46.	2024-03-28 12:53:28.569815	9	WDL-005943998-1
461619	2	0	2	2014-08-05 10:07:46.	2024-03-28 12:53:28.569815	10	WDL-005943998-1
461619	2	0	2	2014-08-05 10:07:46.	2024-03-28 12:53:28.569815	11	spL1ml82N4o
461619	2	0	2	2014-08-05 10:07:46.	2024-03-28 12:53:28.569815	12	WDL-005943998-1