Skip to Main Content
  • Questions
  • How do I write a SQL to display the number of months between two dates?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: May 11, 2016 - 7:11 pm UTC

Last updated: May 13, 2016 - 5:58 am UTC

Version: 3.2.20.09

Viewed 1000+ times

You Asked

I have a database with over a million IDs with income and associated start and end dates as below, but I have difficulties breaking this down by ID per month:

ID INCOME START_DATE END_DATE
1 2000 02/01/2016 05/31/2016
2 1000 01/01/2016 04/30/2016

The outcome should be as follows:
ID INCOME MONTH
1 2000 05/2016
1 2000 04/2016
1 2000 03/2016
1 2000 02/2016
2 1000 04/2016
2 1000 03/2016
2 1000 02/2016
2 1000 01/2016



How do I write the SQL query to achieve the above outcome without using “connect by” or all_objects(I seem to have huge performance issues using these and have to kill the query after an hour and a half)?

and Connor said...

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Dave, May 12, 2016 - 11:12 am UTC

Thank you Connor. I have tried to implement the solution in the reference but I am still not entirely sure how to go about breaking it down for each month within the date range. Please help. Also, when I use the connect by operation, I get a 'ORA-30009: Not enough memory for CONNECT BY operation' error message which based on my research the solution would be to do the following: In WORKAREA_SIZE_POLICY=AUTO mode, set PGA_AGGREGATE_TARGET to a reasonably larger value. Or, in WORKAREA_SIZE_POLICY=MANUAL mode, set SORT_AREA_SIZE to a reasonably larger value. but I can't change any of these settings unfortunately due to permissions restrictions. Please help with the query.
Connor McDonald
May 13, 2016 - 1:46 am UTC

SQL> create table t ( id int, income int, s date, e date );

Table created.

SQL>
SQL> insert into t values (1,2000,date '2016-02-01', date '2016-05-31');

1 row created.

SQL> insert into t values (2,1000,date '2016-01-01', date '2016-04-30');

1 row created.

SQL>
SQL> select t.id,t.income,add_months(s,column_value-1) d
  2  from t,
  3    table(
  4       cast(multiset(select rownum r from   dual connect by level <= round(months_between(t.e,t.s)) )
  5         as sys.ODCINUMBERLIST));

        ID     INCOME D
---------- ---------- ---------
         1       2000 01-FEB-16
         1       2000 01-MAR-16
         1       2000 01-APR-16
         1       2000 01-MAY-16
         2       1000 01-JAN-16
         2       1000 01-FEB-16
         2       1000 01-MAR-16
         2       1000 01-APR-16

8 rows selected.


A reader, May 13, 2016 - 3:10 am UTC

This is very useful. Many thanks to you, Connor!

Followup: I have come across some data that are not displayed correctly though. So for an ID with income and start and end dates of 11/23/2011 and 04/01/2012 respectively, the displayed outcome for this member is as follows:

Data in table:
ID INCOME START_DATE END_DATE
10 500 11/23/2011 04/01/2012

Outcome for the above member is as follows:
ID INCOME MONTH
10 500 02/23/2012
10 500 01/23/2012
10 500 12/23/2011
10 500 11/23/2011

As you can see, it's omitting 03/23/2012 month. Is this due to the round function? How would one modify it to accommodate dates that don't start on the 1st as in the example above? Thanks again.
Connor McDonald
May 13, 2016 - 5:58 am UTC

Ah...your original example only had month boundaries :-)

Assuming your want to include any partial month, simply change

from t

to

from ( select id, income, trunc(s,'MM') s, last_day(e) e ) t

Hope this helps.

A reader, May 13, 2016 - 1:28 pm UTC

Thank you!!Just what I needed to make it work.