So I have been tasked to write a query involving dates for a contract management system we have. The database stores the date the contract's primary terms expired, the frequency that the contract auto-renews, and the notice period required to make a change to the contract. Here is a query with some sample data:
WITH EX_DATA AS ( SELECT '28-FEB-2008' EXPIRE_DT, 30 TERM_NOTICE, 'YEARLY' NOTICE_FREQ, 12 NOTICE_MONTHS FROM DUAL UNION ALL SELECT '1-MAR-2005', 60, 'YEARLY', 12 FROM DUAL UNION ALL SELECT '1-DEC-2007', 30, 'MONTHLY', 1 FROM DUAL ) SELECT * FROM EX_DATA;
I need to return the following data: the next day the contract expires and the day we have to give them notice. For example, in the first set of data above, the contract expires 2/28/2008, it auto-renews every year, and we have to give them 30 days notice of any change. So, today is 2/8. The next day the contract expires is 2/28/2013. Next month, the date would change to 2/28/2014. That's the first field I need to compute. The second is we have to give them 30 days notice if we want to renegotiate the contract. So, we our deadline for that is 30 days prior to 2/28, or 1/29/2013. Again, after 2/28, that will change to 1/29/2014.
In the last example, since it has a monthly renewal, it expires next on 3/1/2013 (then 4/1, 5/1, so on). With a 30 day notice period, they have to be notified 30 days prior to the next expiration date.
The NOTICE_MONTHS column is just an extra information field that has the month count of the auto-renewal, 12 for yearly and 1 for monthly. This might be helpful in the query.
I've tried several different ways of doing this but have not yet come out with a 100% foolproof result. I was taking the month and day and converting to CHAR then converting that back to DATE which gave me contract expiration for this current month. But when I tried applying other logic, I'd get stuck on an error that resulted from leap year.
Any help would be appreciated.