I am trying to create a new column number of week (wk_no_in_month) in that month like the following output. To get week number is W - week number in a month but due to a new week begins every Saturday and on the first of every month it's not as simple I first thought. As you can see wk_no_in_month there 5 weeks in July and 1st week in August week number wrapped around back to 1 and so on.
The script was a solution carry on from my last post. I started to wrestle with it, but I right now I facing to find week number in a month. Please explain to me where I went wrong.
with test_data as (
select 2021 as fy
from dual
), fysd as (
select to_date(to_char(fy - 1) || '0701', 'yyyymmdd') as fy_start_dte
from test_data
), fy_dates as (
select fy_start_dte + level - 1 as dt
from fysd connect by level <= add_months(fy_start_dte, 12) - fy_start_dte )
select dt,
greatest( trunc(dt + 2, 'iw') - 2, trunc(dt, 'mm') ) as wk_start_dte,
least ( trunc(dt + 2, 'iw') + 4, last_day(dt) ) as wk_end_dte,
dense_rank() over (order by greatest(trunc(dt + 2, 'iw') - 2, trunc(dt, 'mm'))) as period_num,
to_char(dt + 2,'iw') - to_char(trunc(dt,'mm'),'iw') wk_no_in_month -- I started to wrestle but is wrong solution
from fy_dates
ORDER BY 1;