Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Anniversary Dates Between Two Periods
Answers
-
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
-
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.
-
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 toadd_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