Struggling with complicated date query

oracle

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.

Best Answer

I'll do it in stages to make it easier to understand.

I put your data into a table called EX_DATA - added a primary key as I assume you'll have one and it makes things a little easier:

create table ex_data
(
  contract_id number(18),
  expire_dt date,
  term_notice number(18),
  notice_freq varchar(20),
  notice_months number
);


insert into ex_data values(1,TO_DATE('01-MAR-2005','DD-MON-YYYY'), 60, 'YEARLY', 12);

insert into ex_data values(2,TO_DATE('01-DEC-2007','DD-MON-YYYY'), 30, 'MONTHLY', 1);

insert into ex_data values(3,TO_DATE('28-FEB-2008','DD-MON-YYYY') ,30, 'YEARLY', 12);

The next expiry date from the first given date will be:

select add_months(expire_dt, notice_months) as next_expiry
from ex_data;

Then, it follows that the notice period is a bit of easy date arithmetic too:

select add_months(expire_dt, notice_months) as next_expiry, 
       add_months(expire_dt, notice_months) - term_notice as notice_date
from ex_data;

Using DUAL as a source, generate expiry dates for lots of years:

select expire_dt as first_expiry_date,
       l as expiry_num,
       add_months(expire_dt,l*notice_months) as all_expiry_dates
from ( select level l from dual connect by level < 100 ) l, ex_data
order by l;

The next expiration date, and the date they should have been contacted for renegotiation:

select first_expiry_date, all_expiry_dates as next_expiry_date, all_expiry_dates - term_notice as renegotiation_date
from
(
select expire_dt as first_expiry_date, l as expiry_period_num, term_notice, add_months(expire_dt, l*notice_months) as all_expiry_dates
from ( select level l from dual connect by level < 100 ) l, ex_data
)
where all_expiry_dates >sysdate
and all_expiry_dates - term_notice < sysdate 
;

But, that's not what I think you're asking. You want the next renewal date where the contact period hasn't yet been hit, which can be done as follows:

select * from (
select contract_id, first_expiry_date, all_expiry_dates as next_expiry_date, all_expiry_dates - term_notice as notification_date, ROW_NUMBER( ) OVER (PARTITION BY
contract_id ORDER BY all_expiry_dates) r
from
(
select contract_id, expire_dt as first_expiry_date, l as expiry_period_num, term_notice, add_months(expire_dt, l*notice_months) as all_expiry_dates, notice_months
from ( select level l from dual connect by level < 100 ) l, ex_data
)
where all_expiry_dates - term_notice > sysdate 
)
where r=1;

Can probably be done with some simple SYSDATE maths along with the source data, but my brain is a little slow this evening ;)

Related Question