Solved

month number in range

Posted on 2012-03-30
5
216 Views
Last Modified: 2012-04-03
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
Comment
Question by:tonMachine100
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 7

Expert Comment

by:waltersnowslinarnold
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

by:waltersnowslinarnold
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

by:tonMachine100
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

Open in new window

0
 
LVL 7

Assisted Solution

by:waltersnowslinarnold
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 32

Accepted Solution

by:
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to keep a record with the highest value 3 59
SQL Query Returns Records in SSMS but not Classic ASP 5 48
Oracle Date 6 41
T-SQL: problem comparing datetime 4 81
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

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

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

Join & Ask a Question