Selecting next month first Friday when current month last day not equal to Friday

oracle-12c

Hi I have my data as below.

Year    Period  Start_Date  End_Date 
2019    6        1-Jun-19    30-Jun-19 
2019    7        1-Jul-19    31-Jul-19

Period will be the month number

start date and end date will be the month start and end dates.

I want to fetch the data as below based on above.

Period  Sequence_no Period_end_dt 
6        1           07-Jun-19 
6        2           14-Jun-19 
6        3           21-Jun-19 
6        4           28-Jun-19 
6        5           05-Jul-19 ( when ever month last day is not equal to Friday i need to display next month first Friday in current month).
7        1           05-Jul-19 
7        2           12-Jul-19 
7        3           19-Jul-19 
7        4           26-Jul-19
7        5           26-Aug-19( when ever month last day is not equal to Friday i need to display next month first Friday in current month).

Best Answer

Oracle fiddle is not available now... :(

In MySQL it can be:

WITH 
cte1 AS (      SELECT 0 num 
         UNION SELECT 1 
         UNION SELECT 2 
         UNION SELECT 3 
         UNION SELECT 4 
         UNION SELECT 5 
         UNION SELECT 6),
cte2 AS (SELECT (@firstday:=CAST(CONCAT(@year:=2019, '-', @period:=5, '-1') AS DATE)) + INTERVAL cte1.num * 7 DAY somedate 
         FROM cte1)
SELECT somedate - INTERVAL 3 + WEEKDAY(somedate) DAY friday 
FROM cte2
HAVING friday BETWEEN @firstday 
                  AND @firstday + INTERVAL 1 MONTH + INTERVAL 5 DAY;

@year and @period emulates the values from source table.

@firstday is used to decrease the amount of calculations.

PS. You may face a problem Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT), (utf8mb4_0900_ai_ci,COERCIBLE) for operation 'between' in MySQL... if so, replace BETWEEN @firstday with BETWEEN @firstday + INTERVAL 1 DAY - INTERVAL 1 DAY in outer query. fiddle. Or calculate first day of month in outer query instead of @firstday variable usage.