## Forum Stats

• 3,838,664 Users
• 2,262,392 Discussions

Discussions

# Anniversary Dates Between Two Periods

2»

• Member Posts: 144 Gold Badge

Hi @User_OK580 ,

```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 (
'DD.MM.') =
'29.02.'
THEN
ELSE
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

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

• 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'
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
```