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.

• Anup Shah: from above query it seems like this query will work for only "expiration type". i believe you have one function for each "expiration type" or do you check the "expiration type" also in function query before returning the Next date.
• cha: use add_months('1-JUN-2016', rownum * 6)
• sukach: @AnupShah Correct. This is all wrapped in a single function. There is a case statement that first determines the expiration type before applying the appropriate logic. The query I provided above is for when expiration type is YTY (year-to-year). @cha I don't see how that works. For example, if the initial expiration was 3/22/2014, simply using rownum * 6 returns a result of 9/22/2014 when the correct response would be 12/1/2014.

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

• Andriy M: You could also complicate it even further to get rid of the subselect (no idea if that would be of any benefit).

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.

• ypercubeᵀᴹ: I don't think you need all this complication. Why the use of all_objects?
• sukach: @ypercube if you have a better idea, i'd love to hear it. the inner query merely returns a list of all months and using all_objects simply as a way of returning a specific number of rows. then, the outer query returns the first result of the inner query that is greater than the current date.
• ypercubeᵀᴹ: I found a way using some functions but I think it's more complicated (although it does not involve using any table.)