Solved

# month number in range

Posted on 2012-03-30
209 Views
I have a table which contains a series of dates. I need some help to get the month number of the date, this month number is determined by the range that is selected - not the month number in the year. I've attached 3 examples to illustrate what i mean.

the table is called t_test, the date column is called dte. month_no is the column that i need some assistance with. Any help is appreciated.
ee-example-mar12.xls
0
Question by:tonMachine100
• 3

LVL 7

Expert Comment

ID: 37786103
Use the following sample query, this should help you;

SELECT
dte, RANK() OVER(PARTITION BY dte ORDER BY dte) month_no
FROM tableName
0

LVL 7

Expert Comment

ID: 37786252
Sorry, you would have to PARTITION BY month, as the requirement suggests;

SELECT
dte, RANK() OVER(PARTITION BY MONTH(dte) ORDER BY dte) month_no
FROM t_test
WHERE whatever the Range is specified..
0

Author Comment

ID: 37786401
im using oracle 10 which doesnt support month(). I've tried:

RANK() OVER(PARTITION BY to_char(dte, 'Mon-YY') ORDER BY dte) month_no

which i presume would achive the same result? but i get this result:
``````dte	MONTH_NO
01-JAN-12	1
03-FEB-12	1
03-FEB-12	1
04-FEB-12	3
05-MAR-12	1
05-APR-12	1
05-MAY-12	1
05-JUN-12	1
10-JUL-12	1
05-AUG-12	1
05-AUG-12	1
15-SEP-12	1
20-OCT-12	1
01-NOV-12	1
05-DEC-12	1
01-JAN-13	1
20-JAN-13	2
02-FEB-13	1
03-MAR-13	1
``````
0

LVL 7

Assisted Solution

waltersnowslinarnold earned 250 total points
ID: 37786448
Oh, please remove PARTITION BY clause from the RANK function. use this as below;

SELECT
dte, DENSE_RANK() OVER(ORDER BY MONTH(dte),dte) month_no
FROM t_test
0

LVL 31

Accepted Solution

awking00 earned 250 total points
ID: 37796813
SQL> select dte, dense_rank() over (order by  trunc(dte,'mm')) month_no
2  from t_test
3  where dte between to_date('01-01-2012','dd-mm-yyyy') and
to_date('03-03-2013','dd-mm-yyyy');

DTE               MONTH_NO
--------- ----------
01-JAN-12          1
03-FEB-12          2
03-FEB-12          2
04-FEB-12          2
05-MAR-12          3
05-APR-12          4
05-MAY-12          5
05-JUN-12          6
10-JUL-12          7
05-AUG-12          8
05-AUG-12          8
15-SEP-12          9
20-OCT-12         10
01-NOV-12         11
05-DEC-12         12
01-JAN-13         13
20-JAN-13         13
02-FEB-13         14
03-MAR-13         15

SQL> select dte, dense_rank() over (order by  trunc(dte,'mm')) month_no
2  from t_test
3  where dte between to_date('10-07-2012','dd-mm-yyyy')
and to_date('20-01-2013','dd-mm-yyyy');

DTE               MONTH_NO
--------- ----------
10-JUL-12          1
05-AUG-12          2
05-AUG-12          2
15-SEP-12          3
20-OCT-12          4
01-NOV-12          5
05-DEC-12          6
01-JAN-13          7
20-JAN-13          7

9 rows selected.

SQL>
SQL> select dte, dense_rank() over (order by  trunc(dte,'mm')) month_no
2  from t_test
3  where dte between to_date('05-03-2012','dd-mm-yyyy')
and to_date('05-08-2012','dd-mm-yyyy');

DTE               MONTH_NO
--------- ----------
05-MAR-12          1
05-APR-12          2
05-MAY-12          3
05-JUN-12          4
10-JUL-12          5
05-AUG-12          6
05-AUG-12          6
0

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

#### 708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!