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:
@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
withBETWEEN @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.