Forum Stats

  • 3,838,664 Users
  • 2,262,392 Discussions
  • 7,900,730 Comments

Discussions

Anniversary Dates Between Two Periods

2»

Answers

  • Jan Gorkow
    Jan Gorkow Member Posts: 144 Gold Badge

    Hi @User_OK580 ,

    I modified my query so it now meets your additional requirements:

    WITH
      anniversaries (ident,
             date_of_birth,
             anniversary_date,
             yrs,
             lower_bound,
             upper_bound)
      AS
       (SELECT table_a.ident,
           table_a.start_date             AS date_of_birth,
           table_a.start_date             AS anniversary_date,
           0                     AS yrs,
           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.yrs + 1  AS yrs,
           anniversaries.lower_bound,
           anniversaries.upper_bound
         FROM anniversaries
        WHERE anniversaries.anniversary_date < anniversaries.upper_bound)
     SELECT anniversaries.ident,
         MIN (anniversaries.yrs)         AS yrs,
         MIN (anniversaries.anniversary_date)  AS anniversary_date
      FROM anniversaries
      WHERE   anniversaries.anniversary_date BETWEEN anniversaries.lower_bound
                            AND anniversaries.upper_bound
         AND anniversaries.yrs IN (10,
                      25,
                      40,
                      50)
    GROUP BY anniversaries.ident;
    

    Best regards

    Jan

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,246 Red Diamond

    Hi9, @User_OK580

    The only anniversaries we want to report on are 10,25,40 and 50 years. 

    In that case, you an do something like this:

       WITH  date_range  AS
    (
    	SELECT TO_DATE ('01/04/2001', 'DD/MM/YYYY') AS range_start_date  
    	SELECT TO_DATE ('31/03/2022', 'DD/MM/YYYY') AS range_end_date  
     	FROM 	dual
    )
    ,  years_wanted  AS
    (
    	SELECT 10 AS yr  	FROM dual UNION ALL
    	SELECT 25 		FROM dual UNION ALL
    	SELECT 40 		FROM dual UNION ALL
    	SELECT 50 		FROM dual  
    )
    SELECT	   a.*, y.yr -- or whatever columns you want
    FROM	   date_range   r
    CROSS JOIN years_wanted y
    JOIN       table_a	a ON a.start_date BETWEEN ADD_MONTHS (r.range_start_date, -12 * yr)
    			   		   AND   ADD_MONTHS (r.range_end_date,  -12 * yr) 
    ORDER BY   a.primary_key  -- or whatever you want
    ;
    

    Of course, I can't test it without any sample data.

  • mathguy
    mathguy Member Posts: 10,591 Blue Diamond
    edited Aug 31, 2021 11:59AM

    The only anniversaries we want to report on are 10,25,40 and 50 years.


    This is important information regarding your problem, why keep it secret?

    With this additional information, I would approach the problem differently.

    First, compute the 10th, 25th, 40th and 50th anniversary for all employees. Then pick just those that fall within your given range. I show how that can be done using the HR.EMPLOYEES table.

    Assuming the number of employees is not too large (say, 500 employees, or even 50,000 employees - which is pretty rare), the whole query will run very fast; and I assume you don't need to run this query too often - perhaps once a month or once a quarter. So "optimization", "using an index" and similar should not be a consideration. I wrote the query in the way that seems, to me, easiest to read and understand (and therefore to maintain).

    I hard-coded the anniversaries you are interested in, as well as the from- and to- dates of the reporting window, right into the query, at the top of the with clause. In real life, h may be a small table you maintain, and the from- and to- dates may be bind variables. I didn't spend too many brain cells on those, since they aren't the core of your question.

    There are (at least) two ways to "add years" to a date. One uses the add_months function. As discussed already, this won't be what you need, in exactly one case: the hire date is 28 February. The other way is to add an interval of "n" years. That fails when you add years to a 29 February date, and the result year is in fact not a leap year. In the query below, I use this method except when the hire date is 29 February - in that case, I revert to add_months.


      with
      h (y) as (               --  which anniversaries we are looking for
        select 10 from dual union all
        select 25 from dual union all
        select 40 from dual union all
        select 50 from dual
      )
    , w (dt_from, dt_to) as (  --  most likely should be bind variables
        select date '2025-04-01', date '2026-03-31' from dual
      )
    , a (employee_id, anniv_years, anniv_date) as (  --  add columns from base table as needed
        select e.employee_id, h.y,
               case when to_char(e.hire_date, 'mmdd') = '0229'
                    then add_months(hire_date, h.y * 12)
                    else hire_date + numtoyminterval(h.y, 'year') end
        from   hr.employees e cross join h
      )
    select anniv_years, employee_id, anniv_date          --  or whatever you need
    from   a cross join w
    where  a.anniv_date between w.dt_from and w.dt_to
    order  by anniv_years desc, anniv_date, employee_id  --  or whatever you need
    ;
    
    
    ANNIV_YEARS  EMPLOYEE_ID  ANNIV_DATE
    -----------  -----------  ----------
             25          102  2026-01-13
    


    User_WI23P