This discussion is archived
14 Replies Latest reply: Jan 10, 2013 7:11 AM by Frank Kulash RSS

Select records based on monthly anniversary date

927215 Newbie
Currently Being Moderated
Hi,

I have a table with a date_added field and I want to select records based on the monthly anniversary date of this field.

eg. ID, Date_added
1, 10-DEC-2012
2, 11-NOV-2012
3, 10-MAR-2012
4, 28-FEB-2012
5, 30-DEC-2012

So For the 10th of Jan 2013, I would want to return records 1 and 3 only

I started looking at the extract function, but this soon falls down for records at the end of the month. For example, on the 28th Feb, I would also want to include records where the date_added day is the 29th, 30th or 31st. So, in the table above I would want to return records 4 and 5, but extract would only return 4.

Is there a simple function to do this month anniversary query - am I missing something very obvious? Or, do I need to write a query to explicitly cope with dates at the end of the month? So far I haven't found a sensible simple solution!

I'm using 11g

thanks
  • 1. Re: Select records based on monthly anniversary date
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    You can do something like
    WHERE   TO_CHAR (date_added, 'DD') = TO_CHAR (target_date, 'DD')
    You can create a function-based index to make this run faster.

    If you have several target dates, put them in a subquery (or a real table) and join. Say target_table contains the dates you're interested in:
    FROM   my_table       m
    JOIN    target_table  t  ON  TO_CHAR (m.date_added, 'DD') = TO_CHAR (t.target_date, 'DD')
  • 2. Re: Select records based on monthly anniversary date
    John Stegeman Oracle ACE
    Currently Being Moderated
    TRUNC is better than to_char

    trunc(date_added, 'MONTH') = trunc(target_date, 'MONTH')

    it's better in that TRUNC is very fast for a date.
  • 3. Re: Select records based on monthly anniversary date
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    John Stegeman wrote:
    TRUNC is better than to_char

    trunc(date_added, 'MONTH') = trunc(target_date, 'MONTH')

    it's better in that TRUNC is very fast for a date.
    I'm not sure how much faster TRUNC is than TO_CHAR; I'm guessing it is a little faster. Also, TRUNC produces a DATE, which has several advantages.

    The trouble is that TRUNC doesn't give the right answer. If target_date is January 10, 2013, then TRUNC (target_date, 'MONTH') is Juanary 1, 2013. That will match dates like January 9, 2013 and Januuary 31, 2013, but not dates like December 10, 2012, or January 10, 2011.
  • 4. Re: Select records based on monthly anniversary date
    927215 Newbie
    Currently Being Moderated
    Thanks guys, but I don't think the solutions help. Briefly, this query is to be executed by a daily scheduled job. The target date will be sysdate, so today is the 10th, the query should select all records where date_added is the 10th of the month. Frank's suggestion works fine for this.

    However, it can fail for dates at the end of the month, for example any date_added on the 31st of the month will only be queried when sysdate is 31st of January, March, May, July, August, October and December. But what I want is for these records to be included for other months where the last day of the month is < 31. For example when the job gets to the 28th Feb, I would want it to return every record whose date_added day of month is 28th, 29th, 30th or 31st.

    Is there a simple way for Oracle to do this or will I need to explicitly code the query to cope with end of month dates?

    thanks for your help so far!
  • 5. Re: Select records based on monthly anniversary date
    AlbertoFaenza Expert
    Currently Being Moderated
    user11259857 wrote:
    Thanks guys, but I don't think the solutions help. Briefly, this query is to be executed by a daily scheduled job. The target date will be sysdate, so today is the 10th, the query should select all records where date_added is the 10th of the month. Frank's suggestion works fine for this.

    However, it can fail for dates at the end of the month, for example any date_added on the 31st of the month will only be queried when sysdate is 31st of January, March, May, July, August, October and December. But what I want is for these records to be included for other months where the last day of the month is < 31. For example when the job gets to the 28th Feb, I would want it to return every record whose date_added day of month is 28th, 29th, 30th or 31st.

    Is there a simple way for Oracle to do this or will I need to explicitly code the query to cope with end of month dates?

    thanks for your help so far!
    Hi,

    I see some flaws in your logic. If your date is 31st you want to check all dates which are the last day of the month. But if sysdate is 28-Feb-2013, would you compare it with 28 in other months or with the last day of the month?

    Regards.
    Al
  • 6. Re: Select records based on monthly anniversary date
    927215 Newbie
    Currently Being Moderated
    hi Al,

    No I don't think there is a flaw. For example, the 30th Jan is not the last day of the month, but 30th June is. So, on the 30th Jan I would only want to query any dates falling on the 30th - February 28th having already been queried on the 28th Jan. However on the 30th June I would want query the 30th of every month, plus the 31st of Jan, Mar, May, Jul, Aug, Oct, Dec. February is a special case being both shorter and a leap year

    thanks

    Richard
  • 7. Re: Select records based on monthly anniversary date
    AlbertoFaenza Expert
    Currently Being Moderated
    user11259857 wrote:
    hi Al,

    No I don't think there is a flaw. For example, the 30th Jan is not the last day of the month, but 30th June is. So, on the 30th Jan I would only want to query any dates falling on the 30th - February 28th having already been queried on the 28th Jan. However on the 30th June I would want query the 30th of every month, plus the 31st of Jan, Mar, May, Jul, Aug, Oct, Dec. February is a special case being both shorter and a leap year

    thanks

    Richard
    Hi Richard,

    so can we assume that your requirements are:

    If the day is last day of that month, then query days of other months being >= day of current month.

    i.e:

    Jan-30 = query only 30 of other months
    Jan-31 = query only 31 of other months

    feb-28 (non leap year) = query 28, 29, 30, 31 of other months
    feb-29 (leap-year) = query 29,30,31 of other months

    Mar-30 = query only 30 of other months
    Apr-30 = query 30 and 31 of other months

    Is this assumption correct?

    If yes, then something like this should work:
    WITH mydates(id, date_added) AS
    (
       SELECT 1, TO_DATE('10-DEC-2012', 'DD-MON-YYYY') FROM DUAL UNION ALL
       SELECT 2, TO_DATE('11-NOV-2012', 'DD-MON-YYYY') FROM DUAL UNION ALL
       SELECT 3, TO_DATE('10-MAR-2012', 'DD-MON-YYYY') FROM DUAL UNION ALL
       SELECT 4, TO_DATE('28-FEB-2012', 'DD-MON-YYYY') FROM DUAL UNION ALL
       SELECT 5, TO_DATE('30-DEC-2012', 'DD-MON-YYYY') FROM DUAL
    )
    SELECT id, date_added  
      FROM mydates
     WHERE (:input_date = LAST_DAY(:input_date) AND TO_CHAR(:input_date,'DD') <= TO_CHAR(date_added, 'DD'))
        OR TO_CHAR(:input_date,'DD') = TO_CHAR(date_added, 'DD');
    
    with input_date = 28/02/2013
    
    id   date_added
    4    28/02/2012
    5    30/12/2012
    Regards.
    Al

    Edited by: Alberto Faenza on Jan 10, 2013 3:21 PM
    Example added
  • 8. Re: Select records based on monthly anniversary date
    927215 Newbie
    Currently Being Moderated
    Hi Al,

    Yes, that's correct.

    I was hoping Oracle would have neat function for querying records based on an anniversary period that would already accommodate this end of month issue.

    thanks

    Richard
  • 9. Re: Select records based on monthly anniversary date
    AlbertoFaenza Expert
    Currently Being Moderated
    I have updated my previous post.
    Check it to see if could be fitting your requirements.

    Regards.
    Al
  • 10. Re: Select records based on monthly anniversary date
    Peter vd Zwan Expert
    Currently Being Moderated
    Hi,

    Try this:
    with dt_list as
    (
    select to_date('10-DEC-2012', 'dd-mon-yyyy') dt from dual union all
    select to_date('11-NOV-2012', 'dd-mon-yyyy') dt from dual union all
    select to_date('10-MAR-2012', 'dd-mon-yyyy') dt from dual union all
    select to_date('28-FEB-2012', 'dd-mon-yyyy') dt from dual union all
    select to_date('30-DEC-2012', 'dd-mon-yyyy') dt from dual 
    )
    ,target_dt as
    (
    select to_date('28-feb-2013', 'dd-mon-yyyy') dt from dual
    )
    
    select
      dl.dt
    
    from
      dt_list       dl
      ,target_dt    td
    
    where
      to_char(td.dt,'dd') = to_char(dl.dt,'dd')
      or ( to_char(td.dt,'dd') = to_char(last_day(td.dt),'dd')         -- target is last day of month
           and to_char(dl.dt,'dd') >= to_char(td.dt,'dd')              -- date list day >= target day
         )
    ;
    
    DT      
    ---------
    28-FEB-12 
    30-DEC-12 
    Regards,

    Peter
  • 11. Re: Select records based on monthly anniversary date
    Solomon Yakobson Guru
    Currently Being Moderated
    I didn't look into leap year, but this should give you a starting point:
    select  *
      from  t
      where 1 = case last_day(to_date(:target_date,'mmddyyyy'))
                  when to_date(:target_date,'mmddyyyy')
                    then case
                           when to_char(date_added,'dd') >= to_char(to_date(:target_date,'mmddyyyy'),'dd')
                             then 1
                         end
                  else case
                           when to_char(date_added,'dd') = to_char(to_date(:target_date,'mmddyyyy'),'dd')
                             then 1
                         end
                end
    /
    For example, target date is 1/10/2013:
    SQL> variable target_date varchar2(8)
    SQL> exec :target_date := '01102013';
    
    PL/SQL procedure successfully completed.
    
    SQL> with t as (
      2             select 1 id,to_date('10-DEC-2012','dd-mon-yyyy') date_added from dual union all
      3             select 2,to_date('11-NOV-2012','dd-mon-yyyy') from dual union all
      4             select 3,to_date('10-MAR-2012','dd-mon-yyyy') from dual union all
      5             select 4,to_date('28-FEB-2012','dd-mon-yyyy') from dual union all
      6             select 5,to_date('30-DEC-2012','dd-mon-yyyy') from dual
      7            )
      8  select  *
      9    from  t
     10    where 1 = case last_day(to_date(:target_date,'mmddyyyy'))
     11                when to_date(:target_date,'mmddyyyy')
     12                  then case
     13                         when to_char(date_added,'dd') >= to_char(to_date(:target_date,'mmddyyyy'),'dd')
     14                           then 1
     15                       end
     16                else case
     17                         when to_char(date_added,'dd') = to_char(to_date(:target_date,'mmddyyyy'),'dd')
     18                           then 1
     19                       end
     20              end
     21  /
    
            ID DATE_ADDE
    ---------- ---------
             1 10-DEC-12
             3 10-MAR-12
    
    SQL> 
    And target date is 2/28/2013:
    SQL> exec :target_date := '02282013';
    
    PL/SQL procedure successfully completed.
    
    SQL> with t as (
      2             select 1 id,to_date('10-DEC-2012','dd-mon-yyyy') date_added from dual union all
      3             select 2,to_date('11-NOV-2012','dd-mon-yyyy') from dual union all
      4             select 3,to_date('10-MAR-2012','dd-mon-yyyy') from dual union all
      5             select 4,to_date('28-FEB-2012','dd-mon-yyyy') from dual union all
      6             select 5,to_date('30-DEC-2012','dd-mon-yyyy') from dual
      7            )
      8  select  *
      9    from  t
     10    where 1 = case last_day(to_date(:target_date,'mmddyyyy'))
     11                when to_date(:target_date,'mmddyyyy')
     12                  then case
     13                         when to_char(date_added,'dd') >= to_char(to_date(:target_date,'mmddyyyy'),'dd')
     14                           then 1
     15                       end
     16                else case
     17                         when to_char(date_added,'dd') = to_char(to_date(:target_date,'mmddyyyy'),'dd')
     18                           then 1
     19                       end
     20              end
     21  /
    
            ID DATE_ADDE
    ---------- ---------
             4 28-FEB-12
             5 30-DEC-12
    
    SQL> 
    SY.
  • 12. Re: Select records based on monthly anniversary date
    927215 Newbie
    Currently Being Moderated
    hi Al,

    Excellent, that works perfectly,

    thanks

    Richard
  • 13. Re: Select records based on monthly anniversary date
    bencol Pro
    Currently Being Moderated
    I'm not matching an supplied date of 28th February to 30th December, but only 31st December
     var in_date varchar2(10)
    
     exec :in_date := '2013-02-28'
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.03
    
     with t as
      2    (select 1 id, date '2012-12-10' date_added from dual
      3  union all select 2, date '2012-11-11' from dual
      4  union all select 3, date '2012-03-10' from dual
      5  union all select 4, date '2012-02-29' from dual
      6  union all select 5, date '2012-12-30' from dual
      7   )
      8  select t.*
      9  from   t
     10   join   (select add_months(to_date(:in_date,'yyyy-mm-dd'),level-date_diff) dt
     11          from   (select ceil(max(date_diff)) date_diff
     12                  from   (select t.id
     13                                ,t.date_added
     14                                ,abs(ceil(months_between(date_added,to_date(:in_date,'yyyy-mm-dd'))))+1 date_diff
     15                          From t
     16                         )
     17                 )
     18          connect by level <= date_diff*2
     19         ) dates
     20    on   t.date_added = dates.dt;
    
            ID DATE_ADDED
    __________ ___________________
             4 29/02/2012 00:00:00
  • 14. Re: Select records based on monthly anniversary date
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Here's a slightly simpler way. See if it meets your business requirements.
    Using the "tables" Peter posted:
    SELECT     d.dt
    ,     t.dt               -- For testing only
    FROM         dt_list     d
    CROSS JOIN  target_dt     t     -- For testing only
    WHERE   EXTRACT ( DAY FROM t.dt + (1 - EXTRACT (DAY FROM d.dt)))
             = 1
    ;
    The query above is good for testing multiple target dates at once. In Production, you'll want to eliminate the 2 marked lines that reference target_dt, and change t.dt to SYSDATE in the WHERE clause.

    Like the other solutions, this query will chose each row from dt_list 12 times a year, with invervals of 28 to 31 days between selections. If dt is the N-th of the month, it will be chosen N days after the end of each month. That means that if dt is between the 1st and 28th of any month, it will be chosen on that same day of the month every month. But if dt is between the 29th and 31st of the month, then it may not be chosen in a given (short) calendar month; it may be chosen twice in the following month, once near the beginning of the month and again near the end, instead.
    For example, if dt is December 30, 2012, then it will be chosen on January 30, 2013, March 2 2013, March 30, 2013, ... Many of the other solutions would put a heavy load on February 28; that is "anniversaries" of 4 different days (28th, 29th, 30th and 31st of any month) would all be observed on February 28th. This solution would spread out the extra load over 4 days (February 28th through March 3rd).

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points