I have a recursive CTE, which generates start and END dates for multiple dates, which is working as expected.
It will stop creating rows before the date crosses midnight or the count(*) of the locations table is exceeded.
With each row the CTE generates I want to associated a location_id. Note there should not be duplicate location_id before one of the stop gaps are reached.
There is no commonality between the start/end dates and the locations so a JOIN didn't seem possible to me.
In the sample code below I tried a cross apply, which gave me the same location_id for each group generated by the recursive code so that isn't working either.
Can someone please suggest a way I can get a unique location_id from the locations table for each row.
Thanks in advance for your expertise and all who answer. My test CASE is below.
Current Result
LOCATION_ID BASE_DATE START_DATE END_DATE
1 08212021 00:00:00 08212021 23:16:00 08212021 23:21:00
1 08212021 00:00:00 08212021 23:26:00 08212021 23:31:00
1 08212021 00:00:00 08212021 23:36:00 08212021 23:41:00
…
…
Expected Result
LOCATION_ID BASE_DATE START_DATE END_DATE
1 08212021 00:00:00 08212021 23:16:00 08212021 23:21:00
2 08212021 00:00:00 08212021 23:26:00 08212021 23:31:00
3 08212021 00:00:00 08212021 23:36:00 08212021 23:41:00
…
...
ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';
CREATE OR REPLACE TYPE nt_date IS TABLE OF DATE;
/
CREATE OR REPLACE FUNCTION generate_dates_pipelined(
p_from IN DATE,
p_to IN DATE
)
RETURN nt_date PIPELINED DETERMINISTIC
IS
v_start DATE := TRUNC(LEAST(p_from, p_to));
v_end DATE := TRUNC(GREATEST(p_from, p_to));
BEGIN
LOOP
PIPE ROW (v_start);
EXIT WHEN v_start >= v_end;
v_start := v_start + INTERVAL '1' DAY;
END LOOP;
RETURN;
END generate_dates_pipelined;
/
CREATE TABLE locations AS
SELECT level AS location_id,
'Door ' || level AS location_name,
CASE round(dbms_random.value(1,3))
WHEN 1 THEN 'A'
WHEN 2 THEN 'T'
WHEN 3 THEN 'T'
END AS location_type
FROM dual
CONNECT BY level <= 20;
WITH input (base_date,start_time) AS (
SELECT
COLUMN_VALUE,
COLUMN_VALUE+
NUMTODSINTERVAL(83760, 'SECOND')
FROM TABLE(generate_dates_pipelined(DATE '2021-08-21',DATE '2021-08-30'))
)
SELECT
location_id,
base_date, start_time + (LEVEL-1) * INTERVAL '10' MINUTE
AS start_date,
start_time + (LEVEL-1) * INTERVAL '10' MINUTE + INTERVAL '5' MINUTE
AS end_date
FROM input i
CROSS APPLY (SELECT location_id from locations)
CONNECT BY (LEVEL-1) * INTERVAL '10' MINUTE < INTERVAL '1' DAY
AND LEVEL <= (SELECT COUNT(*) FROM locations)
AND start_time + (LEVEL-1) * INTERVAL '10' MINUTE < TRUNC(start_time) + INTERVAL '1' DAY;