Another Method can be as follows-------------------- I created my date column using recursive CTE table--------- WITH recursive Date_Ranges AS ( select '2022-01-01' as Date union all select Date + interval 1 day from Date_Ranges where Date < '2022-12-31'), tab as( select *,dayofweek(Date) as daynum,dayname(Date) dayname ,case when dayofweek(Date)=6 then row_number() over(partition by dayofweek(Date) order by dayofweek(Date)) else 0 end as FridayNum from Date_Ranges order by Date) select * from tab where mod(FridayNum,2)0;
#feedback: 1. Please type the code before recording the video; don't waste the viewer's time by typing, focus on the lesson instead of typing and correcting mistakes 2. Comment the code 3. Enable line numbers 4. Store constants in variables with proper names to make the code more readable 5. Indent the code
[with r_cte as (select case when datepart(dw, '2023-01-01') > 6 then dateadd(day, 6 - datepart(dw, '2023-01-01') + 7, datepart(dw, '2023-01-01')) else dateadd(day, 6 - datepart(dw, '2023-01-01'), '2023-01-01') end as first_friday, 14 as days, 1 as factor from dual union all select dateadd(day, days*factor, first_friday), days, factor from r_cte where Year(dateadd(day, days*factor, first_friday)) = 2023) select * from r_cte];
