- 3,714,815 Users
- 2,242,633 Discussions
- 7,845,076 Comments
Forum Stats
Discussions
Categories
- 12 Data
- 362.2K Big Data Appliance
- 6 Data Science
- 1.5K Databases
- 447 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 22 Multilingual Engine
- 487 MySQL Community Space
- 3 NoSQL Database
- 7.6K Oracle Database Express Edition (XE)
- 2.7K ORDS, SODA & JSON in the Database
- 415 SQLcl
- 42 SQL Developer Data Modeler
- 184.8K SQL & PL/SQL
- 21K SQL Developer
- 1.8K Development
- 3 Developer Projects
- 32 Programming Languages
- 135K Development Tools
- 7 DevOps
- 3K QA/Testing
- 236 Java
- 4 Java Learning Subscription
- 10 Database Connectivity
- 65 Java Community Process
- Java 25
- 8 Java APIs
- 141.1K Java Development Tools
- 5 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 134 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 10 Java SE
- 13.8K Java Security
- 3 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 118 LiveLabs
- 28 Workshops
- 9 Software
- 3 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 3 Deutsche Oracle Community
- 10 Español
- 1.9K Japanese
- 2 Portuguese
Week is number of weeks in that month
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;
Best Answers
-
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 trunc(dt + 2,'iw') ) as period_num, dense_rank() over( partition by trunc(dt,'mm') order by trunc(dt + 2,'iw') ) wk_no_in_month from fy_dates order by 1 / DT WK_START_ WK_END_DT PERIOD_NUM WK_NO_IN_MONTH --------- --------- --------- ---------- -------------- 01-JUL-20 01-JUL-20 03-JUL-20 1 1 02-JUL-20 01-JUL-20 03-JUL-20 1 1 03-JUL-20 01-JUL-20 03-JUL-20 1 1 04-JUL-20 04-JUL-20 10-JUL-20 2 2 05-JUL-20 04-JUL-20 10-JUL-20 2 2 06-JUL-20 04-JUL-20 10-JUL-20 2 2 07-JUL-20 04-JUL-20 10-JUL-20 2 2 08-JUL-20 04-JUL-20 10-JUL-20 2 2 09-JUL-20 04-JUL-20 10-JUL-20 2 2 10-JUL-20 04-JUL-20 10-JUL-20 2 2 11-JUL-20 11-JUL-20 17-JUL-20 3 3 12-JUL-20 11-JUL-20 17-JUL-20 3 3 13-JUL-20 11-JUL-20 17-JUL-20 3 3 14-JUL-20 11-JUL-20 17-JUL-20 3 3 15-JUL-20 11-JUL-20 17-JUL-20 3 3 16-JUL-20 11-JUL-20 17-JUL-20 3 3 17-JUL-20 11-JUL-20 17-JUL-20 3 3 18-JUL-20 18-JUL-20 24-JUL-20 4 4 19-JUL-20 18-JUL-20 24-JUL-20 4 4 20-JUL-20 18-JUL-20 24-JUL-20 4 4 21-JUL-20 18-JUL-20 24-JUL-20 4 4 22-JUL-20 18-JUL-20 24-JUL-20 4 4 23-JUL-20 18-JUL-20 24-JUL-20 4 4 24-JUL-20 18-JUL-20 24-JUL-20 4 4 25-JUL-20 25-JUL-20 31-JUL-20 5 5 26-JUL-20 25-JUL-20 31-JUL-20 5 5 27-JUL-20 25-JUL-20 31-JUL-20 5 5 28-JUL-20 25-JUL-20 31-JUL-20 5 5 29-JUL-20 25-JUL-20 31-JUL-20 5 5 30-JUL-20 25-JUL-20 31-JUL-20 5 5 31-JUL-20 25-JUL-20 31-JUL-20 5 5 01-AUG-20 01-AUG-20 07-AUG-20 6 1 02-AUG-20 01-AUG-20 07-AUG-20 6 1 03-AUG-20 01-AUG-20 07-AUG-20 6 1 04-AUG-20 01-AUG-20 07-AUG-20 6 1 05-AUG-20 01-AUG-20 07-AUG-20 6 1 06-AUG-20 01-AUG-20 07-AUG-20 6 1 07-AUG-20 01-AUG-20 07-AUG-20 6 1 08-AUG-20 08-AUG-20 14-AUG-20 7 2 09-AUG-20 08-AUG-20 14-AUG-20 7 2 10-AUG-20 08-AUG-20 14-AUG-20 7 2 11-AUG-20 08-AUG-20 14-AUG-20 7 2
SY.
-
For any given date, the "week-of-the-month" number for that date can be computed directly, with some simple date arithmetic depending solely on the input date - no need for analytic functions on top of other analytic functions. Even for a calendar table, I believe this direct computation will be more efficient than the collective computation using dense ranks and such.
Example (note that the computation is in closed form for each input date, even though the example shows a calendar output):
with dates (dt) as ( select date '2020-07-01' + level - 1 from dual connect by level <= 45 ) select dt, (next_day(dt, 'Sat') - next_day(trunc(dt, 'mm'), 'Sat'))/7 + 1 as week_no_in_month from dates order by dt ; DT WEEK_NO_IN_MONTH --------------- ---------------- 01-Jul-2020 Wed 1 02-Jul-2020 Thu 1 03-Jul-2020 Fri 1 04-Jul-2020 Sat 2 05-Jul-2020 Sun 2 06-Jul-2020 Mon 2 07-Jul-2020 Tue 2 08-Jul-2020 Wed 2 09-Jul-2020 Thu 2 10-Jul-2020 Fri 2 11-Jul-2020 Sat 3 12-Jul-2020 Sun 3 13-Jul-2020 Mon 3 14-Jul-2020 Tue 3 15-Jul-2020 Wed 3 16-Jul-2020 Thu 3 17-Jul-2020 Fri 3 18-Jul-2020 Sat 4 19-Jul-2020 Sun 4 20-Jul-2020 Mon 4 21-Jul-2020 Tue 4 22-Jul-2020 Wed 4 23-Jul-2020 Thu 4 24-Jul-2020 Fri 4 25-Jul-2020 Sat 5 26-Jul-2020 Sun 5 27-Jul-2020 Mon 5 28-Jul-2020 Tue 5 29-Jul-2020 Wed 5 30-Jul-2020 Thu 5 31-Jul-2020 Fri 5 01-Aug-2020 Sat 1 02-Aug-2020 Sun 1 03-Aug-2020 Mon 1 04-Aug-2020 Tue 1 05-Aug-2020 Wed 1 06-Aug-2020 Thu 1 07-Aug-2020 Fri 1 08-Aug-2020 Sat 2 09-Aug-2020 Sun 2 10-Aug-2020 Mon 2 11-Aug-2020 Tue 2 12-Aug-2020 Wed 2 13-Aug-2020 Thu 2 14-Aug-2020 Fri 2
-
Day names are NLS dependent:
SQL> alter session set nls_date_language=french; Session altered. SQL> with dates (dt) as ( 2 select date '2020-07-01' + level - 1 3 from dual 4 connect by level <= 45 5 ) 6 select dt, (next_day(dt, 'Sat') - next_day(trunc(dt, 'mm'), 'Sat'))/7 + 1 as week_no_in_month 7 from dates 8 order by dt 9 ; select dt, (next_day(dt, 'Sat') - next_day(trunc(dt, 'mm'), 'Sat'))/7 + 1 as week_no_in_month * ERROR at line 6: ORA-01846: not a valid day of the week SQL>
SY.
Answers
-
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 trunc(dt + 2,'iw') ) as period_num, dense_rank() over( partition by trunc(dt,'mm') order by trunc(dt + 2,'iw') ) wk_no_in_month from fy_dates order by 1 / DT WK_START_ WK_END_DT PERIOD_NUM WK_NO_IN_MONTH --------- --------- --------- ---------- -------------- 01-JUL-20 01-JUL-20 03-JUL-20 1 1 02-JUL-20 01-JUL-20 03-JUL-20 1 1 03-JUL-20 01-JUL-20 03-JUL-20 1 1 04-JUL-20 04-JUL-20 10-JUL-20 2 2 05-JUL-20 04-JUL-20 10-JUL-20 2 2 06-JUL-20 04-JUL-20 10-JUL-20 2 2 07-JUL-20 04-JUL-20 10-JUL-20 2 2 08-JUL-20 04-JUL-20 10-JUL-20 2 2 09-JUL-20 04-JUL-20 10-JUL-20 2 2 10-JUL-20 04-JUL-20 10-JUL-20 2 2 11-JUL-20 11-JUL-20 17-JUL-20 3 3 12-JUL-20 11-JUL-20 17-JUL-20 3 3 13-JUL-20 11-JUL-20 17-JUL-20 3 3 14-JUL-20 11-JUL-20 17-JUL-20 3 3 15-JUL-20 11-JUL-20 17-JUL-20 3 3 16-JUL-20 11-JUL-20 17-JUL-20 3 3 17-JUL-20 11-JUL-20 17-JUL-20 3 3 18-JUL-20 18-JUL-20 24-JUL-20 4 4 19-JUL-20 18-JUL-20 24-JUL-20 4 4 20-JUL-20 18-JUL-20 24-JUL-20 4 4 21-JUL-20 18-JUL-20 24-JUL-20 4 4 22-JUL-20 18-JUL-20 24-JUL-20 4 4 23-JUL-20 18-JUL-20 24-JUL-20 4 4 24-JUL-20 18-JUL-20 24-JUL-20 4 4 25-JUL-20 25-JUL-20 31-JUL-20 5 5 26-JUL-20 25-JUL-20 31-JUL-20 5 5 27-JUL-20 25-JUL-20 31-JUL-20 5 5 28-JUL-20 25-JUL-20 31-JUL-20 5 5 29-JUL-20 25-JUL-20 31-JUL-20 5 5 30-JUL-20 25-JUL-20 31-JUL-20 5 5 31-JUL-20 25-JUL-20 31-JUL-20 5 5 01-AUG-20 01-AUG-20 07-AUG-20 6 1 02-AUG-20 01-AUG-20 07-AUG-20 6 1 03-AUG-20 01-AUG-20 07-AUG-20 6 1 04-AUG-20 01-AUG-20 07-AUG-20 6 1 05-AUG-20 01-AUG-20 07-AUG-20 6 1 06-AUG-20 01-AUG-20 07-AUG-20 6 1 07-AUG-20 01-AUG-20 07-AUG-20 6 1 08-AUG-20 08-AUG-20 14-AUG-20 7 2 09-AUG-20 08-AUG-20 14-AUG-20 7 2 10-AUG-20 08-AUG-20 14-AUG-20 7 2 11-AUG-20 08-AUG-20 14-AUG-20 7 2
SY.
-
For any given date, the "week-of-the-month" number for that date can be computed directly, with some simple date arithmetic depending solely on the input date - no need for analytic functions on top of other analytic functions. Even for a calendar table, I believe this direct computation will be more efficient than the collective computation using dense ranks and such.
Example (note that the computation is in closed form for each input date, even though the example shows a calendar output):
with dates (dt) as ( select date '2020-07-01' + level - 1 from dual connect by level <= 45 ) select dt, (next_day(dt, 'Sat') - next_day(trunc(dt, 'mm'), 'Sat'))/7 + 1 as week_no_in_month from dates order by dt ; DT WEEK_NO_IN_MONTH --------------- ---------------- 01-Jul-2020 Wed 1 02-Jul-2020 Thu 1 03-Jul-2020 Fri 1 04-Jul-2020 Sat 2 05-Jul-2020 Sun 2 06-Jul-2020 Mon 2 07-Jul-2020 Tue 2 08-Jul-2020 Wed 2 09-Jul-2020 Thu 2 10-Jul-2020 Fri 2 11-Jul-2020 Sat 3 12-Jul-2020 Sun 3 13-Jul-2020 Mon 3 14-Jul-2020 Tue 3 15-Jul-2020 Wed 3 16-Jul-2020 Thu 3 17-Jul-2020 Fri 3 18-Jul-2020 Sat 4 19-Jul-2020 Sun 4 20-Jul-2020 Mon 4 21-Jul-2020 Tue 4 22-Jul-2020 Wed 4 23-Jul-2020 Thu 4 24-Jul-2020 Fri 4 25-Jul-2020 Sat 5 26-Jul-2020 Sun 5 27-Jul-2020 Mon 5 28-Jul-2020 Tue 5 29-Jul-2020 Wed 5 30-Jul-2020 Thu 5 31-Jul-2020 Fri 5 01-Aug-2020 Sat 1 02-Aug-2020 Sun 1 03-Aug-2020 Mon 1 04-Aug-2020 Tue 1 05-Aug-2020 Wed 1 06-Aug-2020 Thu 1 07-Aug-2020 Fri 1 08-Aug-2020 Sat 2 09-Aug-2020 Sun 2 10-Aug-2020 Mon 2 11-Aug-2020 Tue 2 12-Aug-2020 Wed 2 13-Aug-2020 Thu 2 14-Aug-2020 Fri 2
-
Day names are NLS dependent:
SQL> alter session set nls_date_language=french; Session altered. SQL> with dates (dt) as ( 2 select date '2020-07-01' + level - 1 3 from dual 4 connect by level <= 45 5 ) 6 select dt, (next_day(dt, 'Sat') - next_day(trunc(dt, 'mm'), 'Sat'))/7 + 1 as week_no_in_month 7 from dates 8 order by dt 9 ; select dt, (next_day(dt, 'Sat') - next_day(trunc(dt, 'mm'), 'Sat'))/7 + 1 as week_no_in_month * ERROR at line 6: ORA-01846: not a valid day of the week SQL>
SY.
-
Yes. Even worse - NEXT_DAY doesn't allow a third argument for NLS_DATE_LANGUAGE. This has been discussed in the past, for example here: https://community.oracle.com/tech/developers/discussion/4023654/make-next-day-independent-of-the-invoking-sessions-nls-date-language and submitted as an enhancement request here: https://community.oracle.com/tech/apps-infra/discussion/4393874/add-optional-nls-date-language-parameter-to-next-day
In any case - to make it entirely independent of NLS settings and without using magic values (a date "known to be" a Saturday), the query can be modified easily as follows. The helper subquery H returns the string SAT, which is the translation of 'Saturday' in the session's current NLS_DATE_LANGUAGE, the same language as will be used for NEXT_DAY.
with dates (dt) as ( ........ ) select d.dt, (next_day(d.dt, h.sat) - next_day(trunc(d.dt, 'mm'), h.sat))/7 + 1 as week_no_in_month from dates d cross join (select to_char(trunc(sysdate, 'iw') + 5, 'Day') as sat from dual) h order by dt ;