Home » SQL & PL/SQL » SQL & PL/SQL » List of Months between 2 dates (12.2)
List of Months between 2 dates [message #673097] |
Mon, 05 November 2018 14:30 |
|
lott42_gmail
Messages: 146 Registered: June 2010
|
Senior Member |
|
|
I've seen where this question was brought up and answered a few times already
Namely, Michel
http://www.orafaq.com/forum/m/671171/?srch=find_months#msg_671171
but what if you have many rows to calculate a "list of Months" between an "end_date" and "begin_date"
with dates as (
select 1 id, sysdate end_date, sysdate - 32 beg_date from dual
union all
SELECT 2 id, SYSDATE end_date, SYSDATE - 63 beg_date FROM dual
),cal as (
SELECT /*distinct*/ id,
add_months(trunc(beg_date,'MONTH'),level - 1) mnth
FROM
dates
CONNECT BY
level <= months_between(end_date,beg_date) + 1
) select id,
to_char(mnth,'MON-YY') month
FROM
cal
order by
id, mnth;
In this example of two distinct rows(id = 1,2)
I get
1 OCT-18
1 NOV-18
1 NOV-18
2 SEP-18
2 OCT-18
2 OCT-18
2 NOV-18
2 NOV-18
2 NOV-18
2 NOV-18
If I include the distinct, I get
1 OCT-18
1 NOV-18
2 SEP-18
2 OCT-18
2 NOV-18
which is what I want but if I add more rows to my "dates" table, my machine locks up on trying to process the data.
How would I get a list of "MON-YY" for each id without my machine crashing??
|
|
|
Re: List of Months between 2 dates [message #673098 is a reply to message #673097] |
Mon, 05 November 2018 14:41 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> with dates as (
2 select 1 id, sysdate end_date, sysdate - 32 beg_date from dual
3 union all
4 SELECT 2 id, SYSDATE end_date, SYSDATE - 63 beg_date FROM dual
5 )
6 select id, to_char(add_months(beg_date,column_value-1),'MON-YY') mnth
7 from dates,
8 table(cast(multiset(select level
9 from dual connect by level <= months_between(end_date,beg_date)+1
10 ) as sys.odciNumberList))
11 /
ID MNTH
---------- ------
1 OCT-18
1 NOV-18
2 SEP-18
2 OCT-18
2 NOV-18
5 rows selected.
|
|
|
|
|
Re: List of Months between 2 dates [message #673110 is a reply to message #673097] |
Tue, 06 November 2018 13:03 |
Solomon Yakobson
Messages: 3284 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Yes, on 12C you can use lateral:
with dates as (
select 1 id, sysdate end_date, sysdate - 32 beg_date from dual
union all
SELECT 2 id, SYSDATE end_date, SYSDATE - 63 beg_date FROM dual
)
select id,
to_char(dt,'MON-YY') months
from dates,
lateral(
select add_months(trunc(beg_date,'mm'),level - 1) dt
from dual
connect by add_months(trunc(beg_date,'mm'),level - 1) <= end_date
)
order by id,
dt
/
ID MONTHS
---------- -------
1 OCT-18
1 NOV-18
2 SEP-18
2 OCT-18
2 NOV-18
SQL>
Or cross apply:
with dates as (
select 1 id, sysdate end_date, sysdate - 32 beg_date from dual
union all
SELECT 2 id, SYSDATE end_date, SYSDATE - 63 beg_date FROM dual
)
select id,
to_char(dt,'MON-YY') months
from dates
cross apply
(
select add_months(trunc(beg_date,'mm'),level - 1) dt
from dual
connect by add_months(trunc(beg_date,'mm'),level - 1) <= end_date
)
order by id,
dt
/
ID MONTHS
---------- -------
1 OCT-18
1 NOV-18
2 SEP-18
2 OCT-18
2 NOV-18
SQL>
SY.
|
|
|
|
|
Re: List of Months between 2 dates [message #673122 is a reply to message #673121] |
Wed, 07 November 2018 15:07 |
|
lott42_gmail
Messages: 146 Registered: June 2010
|
Senior Member |
|
|
SY
Quote:What do you mean it didn't work
Quote:I tried a version yesterday and it wasn't pretty nor did it work
Sorry for the confusion in my message. YOUR code samples work great. I was trying to say "I tried my OWN version of the code yesterday too and it wasn't pretty nor did it work). So thanks for adding your examples
|
|
|
Goto Forum:
Current Time: Fri Sep 27 11:10:00 CDT 2024
|