CODEWITHSUNDEEP
sqldatabasedateplsqloracle11g
eric
eric

Reputation: 31

Banding a date range in oracle

I am looking to break a row into month bands in Oracle 11.

For instance, say I have schema:

CREATE TABLE MY_TABLE (
  A_FIELD VARCHAR2(45),
  START_DATE DATE,
  END_DATE DATE
);

With an entry such as: "foo", 1-1-2011, 1-1-2012

Is there a way to query the table such that I get a result set like:

"foo", Jan-11
"foo", Feb-11
"foo", Mar-11
"foo", Apr-11
"foo", May-11
.....
"foo", Jan-12

I have been trying various methods with CONNECT BY LEVEL, MONTHS_BETWEEN, etc, but I can't seem to get it quite right.

Thanks all for your help.

Upvotes: 2

Views: 459

Answers (1)

TrojanName
TrojanName

Reputation: 5365

Try this. There are a couple of assumptions in this solution. One being that there are no more than 200 months between end_date and start_date - you can adjust the 200 in the query as you wish. The second assumption is that the day of the month is always the same. If that's not the case, you can tweak the query easily enough to ignore the day entirely. But it should get you 99% of the way there - just let me know if you need clarification. 

select a.A_FIELD, to_char(add_months(a.START_DATE,r), 'Mon-yy') as display_month
from MY_TABLE a, (
    select rownum-1 r 
    from all_objects
    where rownum <= 200) b
where add_months(trunc(a.START_DATE,'mm'),r) <= trunc(a.END_DATE,'mm')
order by A_FIELD, b.r

Upvotes: 3

Related Questions