Get day number when all I have is day of the week

Question

I was interested in MT0's answer on this question using intervals and dates. I was working through trying to find a different way to answer the question and I started to wonder about something.

Using just the intervals that MT0 set up:

with weekly_shifts(shift_date,start_time,end_time) as
(SELECT 'MON', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL UNION ALL
 SELECT 'TUE', INTERVAL '10:00' HOUR TO MINUTE, INTERVAL '19:00' HOUR TO MINUTE FROM DUAL UNION ALL
 SELECT 'WED', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL UNION ALL
 SELECT 'THU', INTERVAL '10:00' HOUR TO MINUTE, INTERVAL '19:00' HOUR TO MINUTE FROM DUAL UNION ALL
 SELECT 'FRI', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL)

If all I have is days of the week in DY format (MON,TUE,WED) and I want to get the number version of the day (2,3,4), what is the easiest way to do that?

My only idea that I could come up with was something like this:

select to_char(next_day(sysdate,shift_date),'D') SHIFT_NUM,
       weekly_shifts.*
from weekly_shifts

Answer 1

You can create a look-up table with all the day names and numbers and then join to that:

CREATE TABLE day_numbers(day_number, day_name) AS
SELECT TO_CHAR(SYSDATE + LEVEL, 'D'),
       TO_CHAR(SYSDATE + LEVEL, 'DY')
FROM   DUAL
CONNECT BY LEVEL <= 7;

Or, you can generate it on the fly as part of the sub-query factoring clause:

WITH day_numbers(day_number, day_name) AS (
  SELECT TO_CHAR(SYSDATE + LEVEL, 'D'),
         TO_CHAR(SYSDATE + LEVEL, 'DY')
  FROM   DUAL
  CONNECT BY LEVEL <= 7
),
 weekly_shifts(shift_date,start_time,end_time) as (
  SELECT 'MON', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL UNION ALL
  SELECT 'TUE', INTERVAL '10:00' HOUR TO MINUTE, INTERVAL '19:00' HOUR TO MINUTE FROM DUAL UNION ALL
  SELECT 'WED', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL UNION ALL
  SELECT 'THU', INTERVAL '10:00' HOUR TO MINUTE, INTERVAL '19:00' HOUR TO MINUTE FROM DUAL UNION ALL
  SELECT 'FRI', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL
)

db<>fiddle here

Answer 2

One possibility is using row_number() and rownum. Need to be thoroughly tested as your example probably simplified:

with weekly_shifts
AS
(
SELECT 'MON' shift_day, INTERVAL '09:00' HOUR TO MINUTE start_time, INTERVAL '18:00' HOUR TO MINUTE end_time FROM DUAL 
UNION ALL
SELECT 'TUE', INTERVAL '10:00' HOUR TO MINUTE, INTERVAL '19:00' HOUR TO MINUTE FROM DUAL 
UNION ALL
SELECT 'WED', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL 
UNION ALL
SELECT 'THU', INTERVAL '10:00' HOUR TO MINUTE, INTERVAL '19:00' HOUR TO MINUTE FROM DUAL 
UNION ALL
SELECT 'FRI', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL
UNION ALL
SELECT 'MON' shift_day, INTERVAL '09:00' HOUR TO MINUTE start_time, INTERVAL '18:00' HOUR TO MINUTE end_time FROM DUAL 
UNION ALL
SELECT 'TUE', INTERVAL '10:00' HOUR TO MINUTE, INTERVAL '19:00' HOUR TO MINUTE FROM DUAL 
UNION ALL
SELECT 'WED', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL 
UNION ALL
SELECT 'THU', INTERVAL '10:00' HOUR TO MINUTE, INTERVAL '19:00' HOUR TO MINUTE FROM DUAL 
UNION ALL
SELECT 'FRI', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL
)
SELECT ROW_NUMBER() OVER (PARTITION BY reset_week ORDER BY reset_week)+1 day_number, shift_day, start_time, end_time, reset_week
  FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY shift_day ORDER BY rownum) reset_week, shift_day, start_time, end_time FROM weekly_shifts
ORDER BY rownum
)
ORDER BY reset_week
/

Output:

day_number  shift_day   start_time              end_time                reset_week
2           MON         +00 09:00:00.000000     +00 18:00:00.000000     1
3           TUE         +00 10:00:00.000000     +00 19:00:00.000000     1
4           WED         +00 09:00:00.000000     +00 18:00:00.000000     1
5           THU         +00 10:00:00.000000     +00 19:00:00.000000     1
6           FRI         +00 09:00:00.000000     +00 18:00:00.000000     1
2           MON         +00 09:00:00.000000     +00 18:00:00.000000     2
3           TUE         +00 10:00:00.000000     +00 19:00:00.000000     2
4           WED         +00 09:00:00.000000     +00 18:00:00.000000     2
5           THU         +00 10:00:00.000000     +00 19:00:00.000000     2
6           FRI         +00 09:00:00.000000     +00 18:00:00.000000     2