I have a month and a day; I am trying to work out the next date when this month and day will exist after a certain date.
For example, if I have a table as follows, where reference_date is in the format 'MMDD' . Please do not blame me for this; This is the official feed format in the UK.
create table tmp_ben_dates ( filing_date date, reference_date varchar2(4)); insert all into tmp_ben_dates values ( to_date('31/12/2011','dd/mm/yyyy'), '1231') into tmp_ben_dates values ( to_date('31/12/2011','dd/mm/yyyy'), '1130') into tmp_ben_dates values ( to_date('31/12/2011','dd/mm/yyyy'), '0101') into tmp_ben_dates values ( to_date('31/07/2011','dd/mm/yyyy'), '0601') into tmp_ben_dates values ( to_date('31/07/2011','dd/mm/yyyy'), '0801') select * from dual;
I would like to return the first number that each reference_date happens after each filing_date . For example, in the first example it will be 12/31/2012, and in the latter case it will be 01/08/2011.
My best attempt so far is this:
with new_date as ( select reference_date , filing_date , add_months( trunc(filing_date,'y') , to_number(substr(reference_date,1,2)) - 1) + to_number(substr(reference_date,3)) - 1 as the_date from tmp_ben_dates ) select filing_date , reference_date , case when filing_date < the_date then next_date else add_months(the_date,12) end from new_date
Which returns the correct results:
FILING_DATE REFE NEXT_DATE ------------------- ---- ------------------- 31/12/2011 00:00:00 1231 31/12/2012 00:00:00 31/12/2011 00:00:00 1130 30/11/2012 00:00:00 31/12/2011 00:00:00 0101 01/01/2012 00:00:00 31/07/2011 00:00:00 0601 01/06/2012 00:00:00 31/07/2011 00:00:00 0801 01/08/2011 00:00:00
However, it is also completely ridiculous; hard to read and understand.
Using interval also possible, but I really don't see how this makes it less confusing to determine what is going on.
with new_date as ( select reference_date , filing_date , trunc(filing_date,'y') + to_yminterval( 'P' || to_char(to_number(substr(reference_date,1,2)) - 1) || 'M') + to_dsinterval( 'P' || to_char(to_number(substr(reference_date,3)) - 1) || 'D') as the_date from tmp_ben_dates ) select filing_date , reference_date , case when filing_date < the_date then the_date else add_months(the_date,12) end as next_date from new_date
Is there anything really obvious I'm missing here? Is there an easy way to do this in SQL?