Forum Stats

  • 3,836,753 Users
  • 2,262,182 Discussions
  • 7,900,094 Comments

Discussions

Categories

Anniversary Dates Between Two Periods

User_OK580
User_OK580 Member Posts: 11 Green Ribbon
in SQL & PL/SQL

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

Tagged:
· Share on TwitterShare on Facebook
«1

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy

    but when i add the above statement

    ORA-00942: table or view does not exist
    · Share on TwitterShare on Facebook
  • User_OK580
    User_OK580 Member Posts: 11 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

    · Share on TwitterShare on Facebook
  • Jan Gorkow
    Jan Gorkow Member Posts: 144 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

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,206 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).

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,578 Red Diamond

    @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.

    · Share on TwitterShare on Facebook
  • Jan Gorkow
    Jan Gorkow Member Posts: 144 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

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,206 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,

    · Share on TwitterShare on Facebook
  • EdStevens
    EdStevens Member Posts: 28,778 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')


    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 10,568 Blue Diamond

    @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.

    · Share on TwitterShare on Facebook
  • User_OK580
    User_OK580 Member Posts: 11 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.

    · Share on TwitterShare on Facebook