Oracle generating schedule rows with an interval

Question

I have some SQL that generates rows for every 5 minutes. How can this be modified to get rid of overlapping times (see below)

Note: Each row should be associated with a location_id with no repeats on the location_id. In this case there should be 25 rows generated so the CONNECT by should be something like SELECT count(*) from locations.

My goal is to create a function that takes in a schedule_id and a start_date in the format 'MMDDYYYY HH24:MI'; and stop creating rows if the next entry will cross midnight; that means some of the location_id may not be used.

The end result is to have the rows placed in the schedule table below. Since I don't have a function yet the schedule_id can be hard coded to 1. I've heard about recursive CTE, would this quality for that method?

Thanks in advance to all who answer and your expertise.


ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

create table schedule(
       schedule_id NUMBER(4),
       location_id number(4),
       start_date DATE,
       end_date DATE,
         CONSTRAINT start_min check (start_date=trunc(start_date,'MI')),   
       CONSTRAINT end_min check (end_date=trunc(end_date,'MI')),
 CONSTRAINT end_gt_start CHECK (end_date >= start_date),
CONSTRAINT same_day CHECK (TRUNC(end_date) = TRUNC(start_date))
      );

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 'G' 
     END AS location_type

FROM   dual
CONNECT BY level <= 25; 

with
   row_every_5_mins as
   ( select trunc(sysdate) + (rownum-1)*5/1440 t_from,
            trunc(sysdate) + rownum*5/1440 t_to
     from dual
      connect by level <= 1440/5
   ) SELECT * from row_every_5_mins;

Current output:

|T_FROM|T_TO|
|-----------------|-----------------|
|08162021 00:00:00|08162021 00:05:00|
|08162021 00:05:00|08162021 00:10:00|
|08162021 00:10:00|08162021 00:15:00|
|08162021 00:15:00|08162021 00:20:00|
…

Desired output

|T_FROM|T_TO|
|-----------------|-----------------|
|08162021 00:00:00|08162021 00:05:00|
|08162021 00:10:00|08162021 00:15:00|
|08162021 00:20:00|08162021 00:25:00|
…

So to "consume" location_id's there should be some order of them on which you want to assign generated lines to locations. And is there any association of schedule_id with locations or they should be used in the same manner for any schedule_id? — astentx

astentx astentx · Accepted Answer · 2021-08-16T21:23:26

You may avoid recursive query or loop, because you essentially need a row number of each row in locations table. So you'll need to provide an appropriate sort order to the analytic function. Below is the query:

with a as (
  select
    date '2021-01-01'
    + to_dsinterval('0 23:30:00')
    as start_dt_param
  from dual
)
, date_gen as (
  select
    location_id
    , start_dt_param
    , start_dt_param + (row_number() over(order by location_id) - 1)
        * interval '10' minute as start_dt
    , start_dt_param + (row_number() over(order by location_id) - 1)
        * interval '10' minute + interval '5' minute as end_dt
from a
  cross join locations
)
select
  location_id
  , start_dt
  , end_dt
from date_gen
where end_dt < trunc(start_dt_param + 1)

LOCATION_ID | START_DT            | END_DT             
----------: | :------------------ | :------------------
          1 | 2021-01-01 23:30:00 | 2021-01-01 23:35:00
          2 | 2021-01-01 23:40:00 | 2021-01-01 23:45:00
          3 | 2021-01-01 23:50:00 | 2021-01-01 23:55:00

UPD: Or if you wish a procedure, then it is even simpler. Because from 12c Oracle has fetch first addition, and analytic function may be simplified to rownum pseudocolumn:

create or replace procedure populate_schedule (
  p_schedule_id in number
  , p_start_date in date
) as
begin
  insert into schedule (schedule_id, location_id, start_date, end_date)
  select
    p_schedule_id
    , location_id
    , p_start_date + (rownum - 1) * interval '10' minute
    , p_start_date + (rownum - 1) * interval '10' minute + interval '5' minute
  from locations
  /*Put your order of location assignment here*/
  order by location_id
  /*The number of 10-minute intervals before midnight from the first end_date*/
  fetch first ((trunc(p_start_date + 1) - p_start_date + 1/24/60*5)*24*60/10) rows only
  ;
  
  commit;
end;
/

begin
  populate_schedule(1, timestamp '2020-01-01 23:37:00');
  populate_schedule(2, timestamp '2020-01-01 23:35:00');
  populate_schedule(3, timestamp '2020-01-01 23:33:00');
end;/

select *
from schedule
order by schedule_id, start_date

SCHEDULE_ID | LOCATION_ID | START_DATE          | END_DATE           
----------: | ----------: | :------------------ | :------------------
          1 |           1 | 2020-01-01 23:37:00 | 2020-01-01 23:42:00
          1 |           2 | 2020-01-01 23:47:00 | 2020-01-01 23:52:00
          2 |           1 | 2020-01-01 23:35:00 | 2020-01-01 23:40:00
          2 |           2 | 2020-01-01 23:45:00 | 2020-01-01 23:50:00
          2 |           3 | 2020-01-01 23:55:00 | 2020-01-02 00:00:00
          3 |           1 | 2020-01-01 23:33:00 | 2020-01-01 23:38:00
          3 |           2 | 2020-01-01 23:43:00 | 2020-01-01 23:48:00
          3 |           3 | 2020-01-01 23:53:00 | 2020-01-01 23:58:00

db<>fiddle here

Oracle generating schedule rows with an interval

3 Answers