Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

month number in range

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
tonMachine100
Asked:
tonMachine100
  • 3
2 Solutions
 
waltersnowslinarnoldCommented:
Use the following sample query, this should help you;

SELECT
dte, RANK() OVER(PARTITION BY dte ORDER BY dte) month_no
FROM tableName
0
 
waltersnowslinarnoldCommented:
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
 
tonMachine100Author Commented:
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

Open in new window

0
 
waltersnowslinarnoldCommented:
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
 
awking00Commented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now