CODEWITHSUNDEEP
sqloracle-databasedateoracle11goracle10g
B-Rad
B-Rad

Reputation: 35

Oracle SQL query workout days between two dates

I require assistance with a SQL query that will produce the days of week between Start_date and End_Date and using the weekday.

Example: between start and End date for Calendar 99 required is the date for Weekday "Wednesday" starting point (seqnum 1) and Weekday Saturday (seqnum2).

In a nutshell Day of week dates between 2 dates depending on calendar and weekdays and seqnum for order in which to be produced.

Any help or ideas would be highly appreciated.

Sample Calendar Table

CREATE TABLE CALENDAR
(
  CALENDAR_NAME  VARCHAR2(500 CHAR),
  START_DATE     VARCHAR2(100 CHAR),
  END_DATE       VARCHAR2(100 CHAR),
  SEQNUM         NUMBER,
  WEEKDAY        VARCHAR2(9 CHAR),
  STARTTIME      VARCHAR2(8 CHAR)
);

Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('99', '2020-07-29', '2021-08-07', 1, 'WEDNESDAY', 
    '17:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('99', '2020-07-29', '2021-08-07', 2, 'SATURDAY', 
    '17:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('179', '2000-01-02', '2021-02-01', 1, 'MONDAY', 
    '18:00:00');
Insert into CALENDARR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('179', '2000-01-02', '2021-02-01', 2, 'WEDNESDAY', 
    '18:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('179', '2000-01-02', '2021-02-01', 3, 'FRIDAY', 
    '18:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('179', '2000-01-02', '2021-02-01', 4, 'SUNDAY', 
    '18:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('772', '2000-01-02', '2021-02-01', 1, 'TUESDAY', 
    '18:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('772', '2000-01-02', '2021-02-01', 2, 'WEDNESDAY', 
    '18:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('772', '2000-01-02', '2021-02-01', 3, 'THURSDAY', 
    '18:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('772', '2000-01-02', '2021-02-01', 4, 'FRIDAY', 
    '18:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('772', '2000-01-02', '2021-02-01', 5, 'SATURDAY', 
    '18:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('772', '2000-01-02', '2021-02-01', 6, 'SUNDAY', 
    '18:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('772', '2000-01-02', '2021-02-01', 7, 'MONDAY', 
    '18:00:00');
COMMIT;

Upvotes: 0

Views: 113

Answers (1)

MT0
MT0

Reputation: 167982

In a nutshell Day of week dates between 2 dates depending on calendar and weekdays and seqnum for order in which to be produced.

If this is to be interpreted as, for each calendar_name, you want to start from the start_date and output the day of the week that matches weekday for each week until you get to the end_date.

Note: It would be better if stored the dates in a date data type.

You can use a recursive sub-query:

WITH dates (calendar_name, dt, end_date) AS (
  SELECT calendar_name,
         NEXT_DAY(TO_DATE(start_date, 'YYYY-MM-DD') - INTERVAL '1' DAY, weekday),
         TO_DATE(end_date, 'YYYY-MM-DD')
  FROM   calendar
  WHERE  NEXT_DAY(TO_DATE(start_date, 'YYYY-MM-DD') - INTERVAL '1' DAY, weekday)
           <= TO_DATE(end_date, 'YYYY-MM-DD')
UNION ALL
  SELECT calendar_name,
         dt + INTERVAL '7' DAY,
         end_date
  FROM   dates
  WHERE  dt + INTERVAL '7' DAY <= end_date
)
SELECT calendar_name,
       dt
FROM   dates
ORDER BY
       calendar_name,
       dt

db<>fiddle here

Upvotes: 1

Related Questions