Your belief that this should not be necessary is correct based on your assumptions. Normally this means the assumptions should be rechecked (unless there is data corruption or a bug). If you run the following you should get the same results from the first query as the second:
DROP TABLE t1;
CREATE TABLE t1 AS (
SELECT to_date('01-FEB-2011','DD-MON-YYYY')+level myDateColumn
FROM dual CONNECT BY level <=120);
select * from t1
where myDateColumn
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');
select myDateColumn from t1
where to_date(myDateColumn)
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');
To check some of the assumptions, can you show the results of the following query?
SELECT myDateColumn, to_date(myDateColumn)
, to_char(myDateColumn,'DD-MON-YY HH.MI.SS PM'), to_char(myDateColumn,'YYYY')
FROM myTable
WHERE to_date(myDateColumn)
BETWEEN to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');
The first three columns should all show identical information and the last should verify that the year is correct.
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 ;)
Best Answer
Overly complex but doesn't need using any other tables:
Tested at SQL-Fiddle