SQL query for next occurrence of specific date

Question

I am working on a function for our database that contains contract data. Each contract expires on a certain date, and then each contract has an expiration type that determines what happens after that primary expiration date.

For example, one expiration type is Year-to-Year. So, if the contract expires on 6/1/2016, after that date, the contract again comes up for expiration on 6/1/2017. My query that handles this expiration type is as follows:

SELECT next_dt
  FROM (SELECT add_months('1-JUN-2016', rownum * 12) next_dt
      FROM all_objects
     WHERE rownum <= 500
     ORDER BY 1)
 WHERE next_dt > trunc(SYSDATE)
   AND rownum = 1;

However, I have one expiration type that says after the initial expiration, the contract again expires the next June 1 or December 1. So, if the contract initially expires on 10/1/2015, the contract comes up for expiration again on 12/1/2015. If it expires on 12/5/2014, it comes up for expiration again on 6/1/2015.

I'm struggling with how to do this. Again, this is a function so we're not limited to a single query to return the desired result.

Any help would be appreciated. Using Oracle 11g.

Answer 1

Overly complex but doesn't need using any other tables:

SELECT ADD_MONTHS(d, 6-MOD(1+MONTHS_BETWEEN(d, TRUNC(d, 'year')),6)) AS next_dt
FROM
  ( SELECT TRUNC(dt, 'month') AS d
    FROM <table> 
  ) x ;

Tested at SQL-Fiddle

Answer 2

I think I found a solution.

SELECT next_dt
  FROM (SELECT trunc(add_months('11-OCT-2016', rownum), 'mm') next_dt
          FROM all_objects
         WHERE rownum <= 500
         ORDER BY 1)
 WHERE next_dt > trunc(SYSDATE)
   AND (to_char(next_dt, 'mm') = '12' OR to_char(next_dt, 'mm') = '06')
   AND rownum = 1;

The query above returns 12/1/2016, which is correct. I used trunc to get me the first day of whatever month was passed. Then I added an additional WHERE clause that limited the results to only December or June. I guess stepping away from the problem last night allowed me to think about it differently this morning.