Anniversary Dates Between Two Periods

User_OK580

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

User_H3J7U

but when i add the above statement

ORA-00942: table or view does not exist

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

Jan Gorkow

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

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

@Jan Gorkow

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

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

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

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

@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

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.