SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to create hourly schedule for date range using custom time slots definition?

BilalMar 12 2020 — edited Mar 16 2020

Hi All,

Oracle Database 19c.

I have the following data in the REGIONS and TIMESLOTS table.

REGIONS

TIMESLOTS

I want to generate all dates for each hour between EFFECTIVE_FROM and EFFECTIVE_TO based on the customised timeslots given in the TIMESLOTS table.

Following the result of how it should look like:

FROM_TS TO_TS TIMESLOT_ID

01-FEB-2020 09:01 01-FEB-2020 10:00 1

01-FEB-2020 10:01 01-FEB-2020 11:00 1

01-FEB-2020 11:01 01-FEB-2020 12:00 1

01-FEB-2020 12:01 01-FEB-2020 13:00 1

01-FEB-2020 13:01 01-FEB-2020 14:00 1

01-FEB-2020 14:01 01-FEB-2020 15:00 1

01-FEB-2020 15:01 01-FEB-2020 16:00 1

01-FEB-2020 16:01 01-FEB-2020 17:00 1

01-FEB-2020 17:01 01-FEB-2020 18:00 2

01-FEB-2020 18:01 01-FEB-2020 19:00 2

01-FEB-2020 19:01 01-FEB-2020 20:00 2

01-FEB-2020 20:01 01-FEB-2020 21:00 3

01-FEB-2020 21:01 01-FEB-2020 22:00 3

01-FEB-2020 22:01 01-FEB-2020 23:00 3

01-FEB-2020 23:01 01-FEB-2020 24:00 3

01-FEB-2020 24:01 02-FEB-2020 01:00 3

02-FEB-2020 01:01 02-FEB-2020 02:00 3

02-FEB-2020 12:01 02-FEB-2020 03:00 3

02-FEB-2020 03:01 02-FEB-2020 04:00 3

02-FEB-2020 04:01 02-FEB-2020 05:00 3

02-FEB-2020 05:01 02-FEB-2020 06:00 3

02-FEB-2020 06:01 02-FEB-2020 07:00 3

02-FEB-2020 07:01 02-FEB-2020 08:00 3

02-FEB-2020 08:01 02-FEB-2020 09:00 3

02-FEB-2020 09:01 02-FEB-2020 10:00 1

02-FEB-2020 10:01 02-FEB-2020 11:00 1

02-FEB-2020 11:01 02-FEB-2020 12:00 1

02-FEB-2020 12:01 02-FEB-2020 13:00 1

02-FEB-2020 13:01 02-FEB-2020 14:00 1

02-FEB-2020 14:01 02-FEB-2020 15:00 1

02-FEB-2020 15:01 02-FEB-2020 16:00 1

02-FEB-2020 16:01 02-FEB-2020 17:00 1

02-FEB-2020 17:01 02-FEB-2020 18:00 2

02-FEB-2020 18:01 02-FEB-2020 19:00 2

02-FEB-2020 19:01 02-FEB-2020 20:00 2

02-FEB-2020 20:01 02-FEB-2020 21:00 3

02-FEB-2020 21:01 02-FEB-2020 22:00 3

02-FEB-2020 22:01 02-FEB-2020 23:00 3

02-FEB-2020 23:01 02-FEB-2020 24:00 3

02-FEB-2020 24:01 03-FEB-2020 01:00 3

03-FEB-2020 01:01 03-FEB-2020 02:00 3

03-FEB-2020 12:01 03-FEB-2020 03:00 3

03-FEB-2020 03:01 03-FEB-2020 04:00 3

03-FEB-2020 04:01 03-FEB-2020 05:00 3

03-FEB-2020 05:01 03-FEB-2020 06:00 3

03-FEB-2020 06:01 03-FEB-2020 07:00 3

03-FEB-2020 07:01 03-FEB-2020 08:00 3

03-FEB-2020 08:01 03-FEB-2020 09:00 3

...

31-JAN-2021 09:01 31-JAN-2021 10:00 1

31-JAN-2021 10:01 31-JAN-2021 11:00 1

31-JAN-2021 11:01 31-JAN-2021 12:00 1

31-JAN-2021 12:01 31-JAN-2021 13:00 1

31-JAN-2021 13:01 31-JAN-2021 14:00 1

31-JAN-2021 14:01 31-JAN-2021 15:00 1

31-JAN-2021 15:01 31-JAN-2021 16:00 1

31-JAN-2021 16:01 31-JAN-2021 17:00 1

31-JAN-2021 17:01 31-JAN-2021 18:00 2

31-JAN-2021 18:01 31-JAN-2021 19:00 2

31-JAN-2021 19:01 31-JAN-2021 20:00 2

31-JAN-2021 20:01 31-JAN-2021 21:00 3

31-JAN-2021 21:01 31-JAN-2021 22:00 3

31-JAN-2021 22:01 31-JAN-2021 23:00 3

31-JAN-2021 23:01 31-JAN-2021 24:00 3

31-JAN-2021 24:01 01-FEB-2021 01:00 3

01-FEB-2021 01:01 01-FEB-2021 02:00 3

01-FEB-2021 12:01 01-FEB-2021 03:00 3

01-FEB-2021 03:01 01-FEB-2021 04:00 3

01-FEB-2021 04:01 01-FEB-2021 05:00 3

01-FEB-2021 05:01 01-FEB-2021 06:00 3

01-FEB-2021 06:01 01-FEB-2021 07:00 3

01-FEB-2021 07:01 01-FEB-2021 08:00 3

01-FEB-2021 08:01 01-FEB-2021 09:00 3

Can I generate this result using a single SQL query?

Any guidance will be greatly appreciated.

The SQL to generate these tables is as below:

--------------------------------------------------------

-- DDL for Table REGIONS

--------------------------------------------------------

CREATE TABLE "REGIONS"

("REGION_ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,

"REGION_NAME" VARCHAR2(30),

"REGION_DESC" VARCHAR2(4000),

"EFFECTIVE_FROM" DATE,

"EFFECTIVE_TO" DATE

);

Insert into REGIONS (REGION_ID,REGION_NAME,REGION_DESC,EFFECTIVE_FROM,EFFECTIVE_TO) values (34,'UWE Bristol','University of the West of England (UWE), Bristol',to_date('01-FEB-20','DD-MON-RR'),to_date('31-JAN-21','DD-MON-RR'));

--------------------------------------------------------

-- DDL for Table TIMESLOTS

--------------------------------------------------------

CREATE TABLE "TIMESLOTS"

( "TIMESLOT_ID" NUMBER GENERATED ALWAYS AS IDENTITY ,

"TIMESLOT_NAME" VARCHAR2(50),

"TIMESLOT_START_TIME" VARCHAR2(5),

"TIMESLOT_END_TIME" VARCHAR2(5),

"TIMESLOT_ORDER_NUM" NUMBER

) ;

Insert into TIMESLOTS (TIMESLOT_ID,TIMESLOT_NAME,TIMESLOT_START_TIME,TIMESLOT_END_TIME,TIMESLOT_ORDER_NUM) values (1,'Normal Work Hours','09:01','17:00',1);

Insert into TIMESLOTS (TIMESLOT_ID,TIMESLOT_NAME,TIMESLOT_START_TIME,TIMESLOT_END_TIME,TIMESLOT_ORDER_NUM) values (2,'Extended Work Hours','17:01','20:00',2);

Insert into TIMESLOTS (TIMESLOT_ID,TIMESLOT_NAME,TIMESLOT_START_TIME,TIMESLOT_END_TIME,TIMESLOT_ORDER_NUM) values (3,'Non Work Hours','20:01','09:00',3);

Many Thanks and

Kind Regards

Bilal

This post has been answered by Frank Kulash on Mar 12 2020

Jump to Answer

Added on Mar 12 2020

6 comments

1,270 views