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 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
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 #673099 is a reply to message #673098] Mon, 05 November 2018 14:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In 12c, you can replace the "TABLE" expression by the "LATERAL" one, see an example at http://www.orafaq.com/forum/mv/msg/95011/589899/#msg_589899

Re: List of Months between 2 dates [message #673101 is a reply to message #673099] Mon, 05 November 2018 15:34 Go to previous messageGo to next message
lott42_gmail
Messages: 146
Registered: June 2010
Senior Member
Thanks, Michel

Quote:
In 12c, you can replace the "TABLE" expression by the "LATERAL" one, see an example at http://www.orafaq.com/forum/mv/msg/95011/589899/#msg_589899
I haven't tried the "Lateral" expression yet BUT the other code you wrote works perfectly!!

Re: List of Months between 2 dates [message #673110 is a reply to message #673097] Tue, 06 November 2018 13:03 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 #673120 is a reply to message #673110] Wed, 07 November 2018 10:14 Go to previous messageGo to next message
lott42_gmail
Messages: 146
Registered: June 2010
Senior Member
Thanks SY for the 12C versions(I tried a version yesterday and it wasn't pretty nor did it work) and thanks again Michel for your solution too
Re: List of Months between 2 dates [message #673121 is a reply to message #673120] Wed, 07 November 2018 11:44 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
What do you mean it didn't work? It should work on any 12C version. Post SQL (along with data sample) and errors you are getting.

SY.
Re: List of Months between 2 dates [message #673122 is a reply to message #673121] Wed, 07 November 2018 15:07 Go to previous message
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

Previous Topic: ORA-00904 on selecting by column name
Next Topic: Last and Sum in group by
Goto Forum:
  


Current Time: Thu Mar 28 04:11:49 CDT 2024