Forum Stats

  • 3,757,133 Users
  • 2,251,200 Discussions
  • 7,869,738 Comments

Discussions

Anniversary Dates Between Two Periods

User_OK580
User_OK580 Member Posts: 10 Green Ribbon

Hi all,


I am having issues with a query to show anniversaries between two dates. I have the below query to show the anniversary date:

SELECT

add_months(start_date,(EXTRACT(YEAR FROM TO_DATE('&TO_DATE','DD/MM/YYYY')) - EXTRACT(YEAR FROM start_date))*12) as anniversary_date

FROM Table_A


WHERE

add_months(start_date,(EXTRACT(YEAR FROM TO_DATE('31/03/2022','DD/MM/YYYY')) - EXTRACT(YEAR FROM start_date))*12) BETWEEN TO_DATE('01/04/2021','DD/MM/YYY') AND TO_DATE('31/03/2022','DD/MM/YYYY')

I would like to run the report between two dates but when i add the above statement in the WHERE clause it is only showing results for the year 2022 and not the From Date (2021).


Any help is appreciated!


TIA

«1

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 457 Bronze Trophy

    but when i add the above statement

    ORA-00942: table or view does not exist
    
  • User_OK580
    User_OK580 Member Posts: 10 Green Ribbon

    You wont have access to the table as its on my server. You can recreate a table on your own server and test it out

  • Jan Gorkow
    Jan Gorkow Member Posts: 133 Gold Badge

    Hi @User_OK580 ,

    I think there is a logical error in your statement: You're extracting the whole year from the end date, sustract the whole year of the start date from it and multiply the result by 12. This is the number of months you the add to the start date. That's why you only get results for the year wich the end date falls into.

    A more flexible solution wich meets your requirements would be the following:

    WITH
      anniversaries (ident,
             anniversary_date,
             lower_bound,
             upper_bound)
      AS
       (SELECT ident,
           start_date                 AS anniversary_date,
           TO_DATE ('[Lower Bound]', 'DD.MM.YYYY')  AS lower_bound,
           TO_DATE ('[Upper Bound]', 'DD.MM.YYYY')  AS upper_bound
         FROM table_a
        UNION ALL
        SELECT anniversaries.ident,
           ADD_MONTHS (anniversaries.anniversary_date, 12)
             AS anniversary_date,
           anniversaries.lower_bound,
           anniversaries.upper_bound
         FROM anniversaries
        WHERE anniversaries.anniversary_date < anniversaries.upper_bound)
     SELECT anniversaries.ident,
         MIN (anniversaries.anniversary_date)  AS anniversary_date
      FROM anniversaries
      WHERE anniversaries.anniversary_date BETWEEN anniversaries.lower_bound
                          AND anniversaries.upper_bound
    GROUP BY anniversaries.ident;
    

    The ident stands for an identifier in the table_a. Lower_bound and upper_bound are the dates you define for the start and the end of the period you want to watch the anniversaries. If the period defined is longer than one year it is possible, then more than one anniversary per ident falls into it. Thats why I take the least using min().

    Best regards

    Jan

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,027 Red Diamond

    HI, @User_OK580

    You wont have access to the table as its on my server. You can recreate a table on your own server and test it out

    Right; to enable people to recreate a table, post CREATE TABLE and INSERT statements for a little sample data, and the exact results you want from the given data. Include examples of any special cases you need to handle (e.g., a row with no anniversaries in the target window.)

    Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,719 Black Diamond

    Oracle month logic isn't always intuitive. If you add months to the last day of the month it will return last day of the resulting month. So assume person was born Feb 28 of a non-leap year, say Feb 28, 2001. Look what ADD_MONTHS will return every leap year:

    SQL> alter session set nls_date_format='FMDD Month, YYYY';
    
    Session altered.
    
    SQL> select add_months(date '2001-02-28',36 + 48 * (level - 1)) from dual connect by level <= 5;
    
    ADD_MONTHS(DATE'20
    ------------------
    29 February, 2004
    29 February, 2008
    29 February, 2012
    29 February, 2016
    29 February, 2020
    
    SQL>
    

    As you can see ADD_MONTHS will not return correct birthday for such person.

    SY.

  • Jan Gorkow
    Jan Gorkow Member Posts: 133 Gold Badge

    Hi @Solomon Yakobson ,

    you are right. Thank you very much for your tip. Thanks god this is the only special case which has to be handled. I've done so in the following query. For someone who was born on 29.02. of a leap year this solutions returns the 28.02. for non leap years and 29.02. for leap years:

    WITH
      anniversaries (ident,
             date_of_birth,
             anniversary_date,
             lower_bound,
             upper_bound)
      AS
       (SELECT ident,
           start_date                 AS date_of_birth,
           start_date                 AS anniversary_date,
           TO_DATE ('[Lower Bound]', 'DD.MM.YYYY')  AS lower_bound,
           TO_DATE ('[Upper Bound]', 'DD.MM.YYYY')  AS upper_bound
         FROM table_a
        UNION ALL
        SELECT anniversaries.ident,
           anniversaries.date_of_birth,
           CASE
             WHEN   TO_CHAR (anniversaries.date_of_birth, 'DD.MM.') =
                 '28.02.'
               AND TO_CHAR (
                   ADD_MONTHS (anniversaries.anniversary_date, 12),
                   'DD.MM.') =
                 '29.02.'
             THEN
              ADD_MONTHS (anniversaries.anniversary_date, 12) - 1
             ELSE
              ADD_MONTHS (anniversaries.anniversary_date, 12)
           END  AS anniversary_date,
           anniversaries.lower_bound,
           anniversaries.upper_bound
         FROM anniversaries
        WHERE anniversaries.anniversary_date < anniversaries.upper_bound)
     SELECT anniversaries.ident,
         MIN (anniversaries.anniversary_date)  AS anniversary_date
      FROM anniversaries
      WHERE anniversaries.anniversary_date BETWEEN anniversaries.lower_bound
                          AND anniversaries.upper_bound
    GROUP BY anniversaries.ident;
    

    Best regards

    Jan

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,027 Red Diamond
    edited Aug 30, 2021 5:40PM

    Hi, @Jan Gorkow

    Thanks god this is the only special case which has to be handled. 

    Actually, there's another special case: October, 1582, at the change from Julian to Gregorian calendars. (I'll bet OP isn't concerned about that.) If "aniversaries" are not exactly 1 year apart, then there will be lots of special cases. For example, if reviews are done every 3 months, and start_date is November 28, 2021; the first review is done on February 28, 2022, but the second review should be on May 28, 2022 (6 months after start_date), not May 31, 2022 (3 "months" after the previous review_date, as ADD_MONTHS reckons it).

    To deal with that problem, you can modify your query like this:

    WITH
     anniversaries (ident,
         date_of_birth,
         anniversary_date,
    	 next_anniversary_num,
         lower_bound,
         upper_bound)
     AS
      (SELECT ident,
        start_date         AS date_of_birth,
        start_date         AS anniversary_date,
        1			 AS next_anniversary_num
        TO_DATE ('[Lower Bound]', 'DD.MM.YYYY') AS lower_bound,
        TO_DATE ('[Upper Bound]', 'DD.MM.YYYY') AS upper_bound
       FROM table_a
      UNION ALL
      SELECT ident,
        date_of_birth,
        ADD_MONTHS (date_of_birth, 12 * next_anniversary_num) AS anniversary_date,
        next_anniversary_num + 1	    			   AS next_anniversry_num
        lower_bound,
        upper_bound
       FROM anniversaries
      WHERE anniversary_date < upper_bound -- or lower_bound, slightly faster
     )
     SELECT ident,
       MIN (anniversary_date) AS anniversary_date
     FROM anniversaries
     WHERE anniversary_date BETWEEN lower_bound
               AND upper_bound
    GROUP BY ident;
    

    I'll test this when OP posts some sample data.

    Actually, I'd do it this way even for true 12-month anniversaries, since it avoids that CASE expression. Of course, the hard-coded 12 needs to be changed to use periods of a different length,

  • EdStevens
    EdStevens Member Posts: 28,453 Gold Crown

    As an aside, please learn to format your code. It makes it much easier to read and comprehend. Here's what your code looks like when following the procedure in the link.

    SELECT
    add_months(start_date,(EXTRACT(YEAR FROM TO_DATE('&TO_DATE','DD/MM/YYYY')) - EXTRACT(YEAR FROM start_date))*12) as anniversary_date
    FROM Table_A
    
    WHERE
    add_months(start_date,(EXTRACT(YEAR FROM TO_DATE('31/03/2022','DD/MM/YYYY')) - EXTRACT(YEAR FROM start_date))*12) BETWEEN TO_DATE('01/04/2021','DD/MM/YYY') AND TO_DATE('31/03/2022','DD/MM/YYYY')
    


  • mathguy
    mathguy Member Posts: 10,066 Gold Crown

    @Jan Gorkow

    this is the only special case which has to be handled. I've done so in the following query. For someone who was born on 29.02. of a leap year this solutions returns the 28.02. for non leap years and 29.02. for leap years

    So, of course, 28 February is not "the only special case which has to be handled" - you immediately go on to state the other "special case", which is 29 February.

    Question back to the OP: How should that be handled? In some locales, the official anniversary for 29 February in a non-leap year is 28 February, but in other locales it is 1 March. Only the OP (or, perhaps, his business user) can answer that question.

  • User_OK580
    User_OK580 Member Posts: 10 Green Ribbon

    Hi all,

    Thanks for your support in this. In the future i will post any questions as per the format intended to answer the question successfully.

    @mathguy if the anniversary date is 29/02 and its a non leap year then the anniversary will be a day behind, 28/02.

    The only anniversaries we want to report on are 10,25,40 and 50 years. We would like to run the report between two dates which will show any employees who have been in the business for 10,25,40 and 50 years when running the report.