Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Creating table for the Productions
-- A Production depicts and Production process. A production process is identified
-- by the PRODUCTION_NUMBER and POSNR, but there can be multiple instances of the
-- same combination, only differing in START_DT, END_TD and/or PRODUCTION_LINE
CREATE TABLE Production
(	
  PRODUCTION_NUMBER NUMBER, 
  POSNR NUMBER, 
  START_DT  TIMESTAMP(0), 
  END_DT TIMESTAMP(0),
  PRODUCTION_LINE NUMBER
);
-- Inserting sample productions
BEGIN
INSERT INTO Production(PRODUCTION_NUMBER, POSNR, START_DT, END_DT, PRODUCTION_LINE)
  VALUES (12344, 1, TO_TIMESTAMP('2024-02-28 14:02:35', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2024-02-29 18:02:35', 'YYYY-MM-DD HH24:MI:SS'), 2);
INSERT INTO Production(PRODUCTION_NUMBER, POSNR, START_DT, END_DT, PRODUCTION_LINE)
  VALUES (12345, 1, TO_TIMESTAMP('2024-02-29 18:22:40', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2024-03-07 18:22:40', 'YYYY-MM-DD HH24:MI:SS'), 1);
INSERT INTO Production(PRODUCTION_NUMBER, POSNR, START_DT, END_DT, PRODUCTION_LINE)
  VALUES (12345, 2, TO_TIMESTAMP('2024-03-05 13:02:35', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2024-03-12 13:02:35', 'YYYY-MM-DD HH24:MI:SS'), 2);
INSERT INTO Production(PRODUCTION_NUMBER, POSNR, START_DT, END_DT, PRODUCTION_LINE)
  VALUES (12345, 2, TO_TIMESTAMP('2024-03-14 14:02:35', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2024-03-16 13:02:35', 'YYYY-MM-DD HH24:MI:SS'), 2);
END;
/
1 rows affected

-- Creating Cardboard Table
-- Cardboards are used for the production process, they have a barcode and are scanned
-- immediately before used in a production.
CREATE TABLE Cardboard
(
    Cardboard_Number varchar2(100), -- The barcode number of the cardboard - the last 10 digits represent the delivery note number
	date_Time TIMESTAMP(0), -- Time of being scanned
	ProductionLine_Number int -- The line number where the cardboard was used
);
-- Inserting sample cardboards
INSERT INTO cardboard VALUES 
('WDL-005943998-1', TIMESTAMP '2014-08-05 10:03:32', 1),
('spL1ml82N4o',TIMESTAMP '2024-02-29 17:13:54', 1),
('WDL-005943998-1',TIMESTAMP '2024-03-01 09:44:42', 1),
('WDL-005943998-1',TIMESTAMP '2024-03-01 10:34:57', 1),
('950024027237',TIMESTAMP '2024-03-01 10:44:57', 1),
('950024027237',TIMESTAMP '2024-03-01 10:52:57', 1),
('WDL-005943998-1',TIMESTAMP '2024-03-01 13:58:43', 2),
('WDL-005943998-1',TIMESTAMP '2024-03-01 13:58:46', 2),
('spL1ml82N4o',TIMESTAMP '2024-03-01 14:09:43', 2),
('WDL-005943998-1',TIMESTAMP '2024-03-12 11:48:36', 2);
10 rows affected

-- Current select for returning cardboards (with an offset) based on 
-- input: production_number = 12345 and posnr = 1
select d.cardboard_number, d.date_time, d.productionline_number
from production a
join lateral
(
    select cardboard_number, date_time, productionline_number
    from (
    SELECT cardboard_number, date_time, productionline_number,
        sum(case when date_time between a.start_dt and a.end_dt then 1 else 0 end) over (
            partition by ProductionLine_Number
            order by date_time
            rows BETWEEN 1 preceding AND 1 following
        ) matches
        from cardboard
    ) table1_with_matches
    where matches > 0
) d on a.production_line = d.productionline_number
where a.production_number = 12345 and a.posnr = 1


CARDBOARD_NUMBER
DATE_TIME
PRODUCTIONLINE_NUMBER


spL1ml82N4o
29-FEB-24 17.13.54
1

WDL-005943998-1
01-MAR-24 09.44.42
1

WDL-005943998-1
01-MAR-24 10.34.57
1

950024027237
01-MAR-24 10.44.57
1

950024027237
01-MAR-24 10.52.57
1


-- Current select for returning Productions (with an offset) based on
-- input: delivery note number = 05943998-1
With Cardboard_offset(cardboard_number, date_time, productionline_number) as (
    select cardboard_number, date_time, productionline_number
    from (
        SELECT cardboard_number, date_time, productionline_number,
        sum(case when Cardboard_Number like CONCAT('%', '05943998-1') then 1 else 0 end) over (
            partition by ProductionLine_Number
            order by date_time
            rows BETWEEN 1 preceding AND 1 following
        ) matches
        from cardboard
    ) table1_with_matches
    where matches > 0
)
SELECT distinct p.production_number,
       p.posnr,
       p.production_line,
       p.start_dt,
       p.end_dt
FROM   production p
       INNER JOIN cardboard_offset c
       ON p.production_line = c.productionline_number
       AND c.date_time BETWEEN p.start_dt AND p.end_dt


PRODUCTION_NUMBER
POSNR
PRODUCTION_LINE
START_DT
END_DT


12345
1
1
29-FEB-24 18.22.40
07-MAR-24 18.22.40

12345
2
2
05-MAR-24 13.02.35
12-MAR-24 13.02.35


-- Question: 
-- I need a query, where input from the user is the PRODUCTION_NUMBER and POSNR.
-- The query should find the Production which is searched for + all additional
-- Productions where any of the delivery note numbers from the searched Production are 
-- used

-- So basically:
-- Input: PRODUCTION_NUMBER = 12345, POSNR = 1, Offset = 1
-- Expected Output: 
-- 112344 - 1 (because delivery note number \'pL1ml82N4o\' was used in this production)
-- 12345 - 1 (because delivery note numer \'05943998-1\' was used in this production)
-- 12345 - 2 (because delivery note numer \'05943998-1\' was used in this production)

-- To sum the steps up: Select the searched PRODUCTION, find all delivery note numbers
-- within this PRODUCTION, find all PRODUCTIONS where any of delivery note numbers is
-- used as well

select 1 from dual;


1


1
CARDBOARD_NUMBER	DATE_TIME	PRODUCTIONLINE_NUMBER
spL1ml82N4o	29-FEB-24 17.13.54	1
WDL-005943998-1	01-MAR-24 09.44.42	1
WDL-005943998-1	01-MAR-24 10.34.57	1
950024027237	01-MAR-24 10.44.57	1
950024027237	01-MAR-24 10.52.57	1
PRODUCTION_NUMBER	POSNR	PRODUCTION_LINE	START_DT	END_DT
12345	1	1	29-FEB-24 18.22.40	07-MAR-24 18.22.40
12345	2	2	05-MAR-24 13.02.35	12-MAR-24 13.02.35