oracle sql generating shiftcodes based on pattern

Question

*I have 3 user inputs

• startdate : 3/14/2022
• focusdate: 4/2/2022
• pattern: 5D,2R,5E,2R ( The code consists of number consecutive days and shift code. for example 5D means 5 consecutive days of shift D. 2R is 2 days restday and 5E is 5 consecutive days of shift E. This can be any combination ex. '1R, 5D, 1R' or '5E,2R'). After the pattern it will repeat in the beginning

I need an oracle sql to output what will be the shift( D or R or E ) on the date 4/2/2022. The pattern will start in 3/14/2022. The output should be R

Answer 1

You can split the pattern and then use arithmetic to find which part of the pattern corresponds to the number of days between your dates.

(Note: you can do it all using simple string functions, which are much faster than regular expressions.)

This will support multiple rows of input:

WITH your_input (startdate, focusdate, pattern) AS (
  SELECT DATE '2022-03-14', DATE '2022-04-02', '5D,2R,5E,2R' FROM DUAL UNION ALL
  SELECT DATE '2022-03-14', DATE '2022-04-03', '5D,2R,5E,2R' FROM DUAL UNION ALL
  SELECT DATE '2022-03-14', DATE '2022-04-04', '5D,2R,5E,2R' FROM DUAL UNION ALL
  SELECT DATE '2022-03-14', DATE '2022-04-05', '5D,2R,5E,2R' FROM DUAL
),
bounds (startdate, focusdate, pattern, spos, epos) AS (
  -- Find the start and end positions for each term in your pattern.
  SELECT startdate,
         focusdate,
         pattern,
         1,
         INSTR(pattern, ',', 1)
  FROM   your_input
UNION ALL
  SELECT startdate,
         focusdate,
         pattern,
         epos + 1,
         INSTR(pattern, ',', epos + 1)
  FROM   bounds
  WHERE  epos > 0
)
SEARCH DEPTH FIRST BY startdate, focusdate, pattern SET order_id,
split_pattern (startdate, focusdate, pattern, pattern_id, pattern_days, pattern_type) AS (
  -- Split the pattern into a number of days and the type character using
  -- the previously found bounds
  SELECT startdate,
         focusdate,
         pattern,
         ROW_NUMBER() OVER (PARTITION BY startdate, focusdate, pattern ORDER BY spos),
         TO_NUMBER(
           CASE epos
           WHEN 0
           THEN SUBSTR(pattern, spos, LENGTH(pattern) - spos )
           ELSE SUBSTR(pattern, spos, epos - 1 - spos)
           END
         ),
         CASE epos
         WHEN 0
         THEN SUBSTR(pattern, -1, 1)
         ELSE SUBSTR(pattern, epos - 1, 1)
         END
  FROM   bounds
),
days (startdate, focusdate, pattern, min_days, max_days, type, total_days) AS (
  -- Find the total days across the pattern
  SELECT startdate,
         focusdate,
         pattern,
         SUM(pattern_days) OVER (
           PARTITION BY startdate, focusdate, pattern
           ORDER BY pattern_id
         ) - pattern_days,
         SUM(pattern_days) OVER (
           PARTITION BY startdate, focusdate, pattern
           ORDER BY pattern_id
         ) - 1,
         pattern_type,
         SUM(pattern_days) OVER (
           PARTITION BY startdate, focusdate, pattern
         )
  FROM   split_pattern
)
-- Match the part of the pattern containing the date.
SELECT startdate,
       focusdate,
       pattern,
       type
FROM   days
WHERE  MOD(focusdate - startdate, total_days) BETWEEN min_days AND max_days

Which outputs:

STARTDATE	FOCUSDATE	PATTERN	TYPE
2022-03-14 00:00:00	2022-04-02 00:00:00	5D,2R,5E,2R	R
2022-03-14 00:00:00	2022-04-03 00:00:00	5D,2R,5E,2R	R
2022-03-14 00:00:00	2022-04-04 00:00:00	5D,2R,5E,2R	E
2022-03-14 00:00:00	2022-04-05 00:00:00	5D,2R,5E,2R	E

fiddle