Monday, March 11, 2013

Who's Birthday is it in "n" number of days?

A friend of mine recently asked me the following question: "I have a table with names and birthdays. How do I find who's birthday is coming up in the next 15 days". Initially this it appears to be a simple question but it's a bit more complex than I originally thought.

I was going to post my solution here, then James Murtagh, from Red Gate, offered me two five-user licenses for their new product called Source Control for Oracle to give away to the readers of this blog. Instead, I'm going to run an informal contest to give away these sets of licenses (each valued at $1475). As with all contests, please read the terms and conditions from Redgate.

So here's the question (similar to my friend's question about birthday's but on the common "EMP" table): Suppose that I'm the HR manager and am planning to recognize the anniversary date that each employee was hired on. I'd like to know all the employees who's anniversary hire date is in the next 30 days.

Please post your solution in the comments section below. Every answer with a correct solution will have their name entered into the draw. Answers must be submitted by end of day on Friday March 15th. I'll announce the winners next week.

Notes:
- Use SYSDATE for today's date. I'll just alter the FIXED_DATE setting in oracle to set the SYDATE value for my testing.  
- Write your query for the default EMP table. If you don't have the EMP table in your schema this article contains the scripts to generate it.

I'm looking forward to everyone's solutions!

Update: Please read the follow up post to see how I tested this solution.

80 comments:

  1. Tricky - very enjoyable!

    At first I thought about using calendar arithmetic -- (SYSDATE - hiredate) modulo 365 -- but realized that leap years would make that calculation a lot trickier. Probably solvable, but too clever.

    Then I thought about creating a 'synthetic' version of hiredate - swapping out the year hired with this calendar year (02-APR-81 would become 02-APR-13), which would let you do some easy filtering on the synthetic hiredate. But I that wouldn't cover the Dec/Jan window, where someone could be inside the 30 days window, but in a different YYYY...

    I settled on using a row generator to get the next 30 days, and then joining this back to the EMP table on date parts.

    SELECT look_ahead.*
    ,emp.empno
    ,emp.ename
    ,emp.hiredate
    FROM
    --look ahead 30 days, including today
    (SELECT TRUNC(SYSDATE)-1 + n AS date_value
    FROM
    --row generator to get numbers 1-30 on
    --from dual
    (SELECT ROWNUM n
    FROM ( SELECT 1 just_a_column
    FROM dual
    GROUP BY CUBE(1,2,3,4,5) )
    WHERE ROWNUM <= 30
    )
    ) look_ahead
    --join on date parts to EMP table
    JOIN emp
    ON TO_CHAR(look_ahead.date_value, 'MON') = TO_CHAR(emp.hiredate, 'MON')
    AND TO_CHAR(look_ahead.date_value, 'DD') = TO_CHAR(emp.hiredate, 'DD')

    ReplyDelete
  2. This SQL should do it:

    SELECT *
    FROM emp e
    WHERE ADD_MONTHS(hiredate, (EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM hiredate)) * 12)
    BETWEEN TRUNC(SYSDATE) + 1 AND TRUNC(SYSDATE) + 31

    ReplyDelete
  3. SELECT hiredate
    FROM (SELECT hiredate,
    TRUNC(SYSDATE) today,
    CASE
    -- person was hired on a leap-day, but this is not a leap year
    -- then treat the person as having been hired on Mar 1
    WHEN TO_CHAR(hiredate, 'mmdd') = '0229'
    AND TO_CHAR(TRUNC(SYSDATE, 'yyyy') + 59, 'mmdd') != '0229'
    THEN
    -- if you want to treat leap-day as Feb 28
    -- TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || '0228', 'yyyymmdd')
    TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || '0301', 'yyyymmdd')
    ELSE
    TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || TO_CHAR(hiredate, 'mmdd'), 'yyyymmdd')
    END
    birthdaythisyear
    FROM scott.emp)
    WHERE birthdaythisyear BETWEEN today AND today + 30


    ReplyDelete
  4. my query only returns the actual anniversary dates within the 30 day window. Simply add columns to pull additional EMP info as needed


    SELECT *
    FROM (SELECT emp.*,
    TRUNC(SYSDATE) today,
    CASE
    -- person was hired on a leap-day, but this is not a leap year
    -- then treat the person as having been hired on Mar 1
    WHEN TO_CHAR(hiredate, 'mmdd') = '0229'
    AND TO_CHAR(TRUNC(SYSDATE, 'yyyy') + 59, 'mmdd') != '0229'
    THEN
    -- if you want to treat leap-day as Feb 28
    -- TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || '0228', 'yyyymmdd')
    TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || '0301', 'yyyymmdd')
    ELSE
    TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || TO_CHAR(hiredate, 'mmdd'), 'yyyymmdd')
    END
    birthdaythisyear
    FROM scott.emp)
    WHERE birthdaythisyear BETWEEN today AND today + 30

    ReplyDelete
  5. Another version that assumes leap-day hiredates in non-leap years will be considered Feb 28

    SELECT x.*
    FROM (SELECT TRUNC(SYSDATE) today,
    emp.*,
    TO_CHAR(SYSDATE, 'yyyy') - TO_CHAR(hiredate, 'yyyy') - 1 years
    FROM scott.emp) x
    WHERE ADD_MONTHS(hiredate, 12 * years) BETWEEN today AND today + 30
    OR ADD_MONTHS(hiredate, 12 * years + 12) BETWEEN today AND today + 30
    ORDER BY hiredate

    ReplyDelete
  6. Hi Martin this is my solution:

    select empno,
    ename,
    hiredate,
    to_date(to_char(hiredate,'DD/MM')||to_char(sysdate,'YYYY'),'DD/MM/YYYY') Anniversary
    from emp
    where to_date(to_char(hiredate,'DD/MM')||to_char(sysdate,'YYYY'),'DD/MM/YYYY')-trunc(sysdate) between 0 and 30;

    Regards.


    Eddie Molina

    ReplyDelete
  7. select ename from emp where hiredate between sysdate and sysdate + 15

    ReplyDelete
  8. It ain't pretty but it works!

    SELECT t.ename,
    t.hiredate,
    to_date(TO_CHAR(t.hiredate, 'DD-MON') || '-' || TO_CHAR(SYSDATE + 30, 'YYYY'), 'DD-MON-YYYY') AS anniv_date
    FROM emp t
    WHERE to_date(TO_CHAR(t.hiredate, 'DD-MON') || '-' || TO_CHAR(SYSDATE + 30, 'YYYY'), 'DD-MON-YYYY') BETWEEN
    SYSDATE AND SYSDATE + 30

    ReplyDelete
  9. Don't know how to make a decent formatting here in the comment - it won't accept PRE tags ;-)

    But here's a piece of SQL that should do the desired thing ;-)

    select empno
    , ename
    , hiredate
    , add_months(
    hiredate
    , 12 * ceil(months_between(sysdate, hiredate) / 12)
    ) next_anniversary
    from scott.emp
    where add_months(
    hiredate
    , 12 * ceil(months_between(sysdate, hiredate) / 12)
    ) <= trunc(sysdate) + 30
    order by next_anniversary
    /

    ReplyDelete
  10. Ok, here goes:


    define no_of_days=30

    SELECT ename
    , hiredate
    FROM emp
    WHERE (ADD_MONTHS(hiredate
    ,12 * GREATEST ((EXTRACT (YEAR FROM SYSDATE)
    -
    EXTRACT (YEAR FROM hiredate)
    )
    ,1
    )
    )
    -
    TRUNC(SYSDATE)
    ) BETWEEN 0 AND &no_of_days
    ;


    Explanation:
    - no_of_days is your 'n'. In your question you define it as 30
    - I calculate the number of years between the year a person was hired and the current year
    - Then I add months to the hiredate. The amount is 12 times the calculated number of years.
    This gives me the aniversary date of this year.
    - That aniversary date minus trunc(sysdate) is nmber of days between now and aniversary, which should be between 0 (it shouldn't be in the past) and no_of_days
    - The 'greatest' is in there to rule out people who start today or in the next 30 days.
    In that case the greatest makes sure I'm given next-years aniversary, which is more then 30 days away



    By the way: the answer to the original question "Who's Birthday is it in "n" number of days?"
    SELECT case n
    when 1 then 'mine'
    else 'I don''t know'
    end "Answer"
    FROM dual
    ;

    ReplyDelete
    Replies
    1. Congrats on winning the contest! Please send me an email with your contact information so that I can pass it along to Red Gate.

      Delete
    2. Hello Martin,

      I think this solution will not work in cases when the next aniversary is in the next year. For instance, if sysdate is Dec 25, 2013, and the aniversary is on Jan 07, the solution will not show that employee.

      Iudith's solution(s) work well in all cases.

      Delete
  11. Here is my solution:

    select * from (
    select emp.*,
    to_date(to_char(HIREDATE,'dd.mm.')||to_char(sysdate,'YYYY'),'dd.mm.yyyy') a_date
    from emp)
    where case when a_date<trunc(sysdate) then ADD_MONTHS(a_date,12) else a_date end between trunc(sysdate) and trunc(sysdate)+30

    cu
    Frank

    ReplyDelete
  12. select *
    from emp e
    where to_date(to_char(e.hiredate,'ddmm')||extract(year from sysdate),'ddmmyyyy') between trunc(sysdate) and sysdate+30
    or to_date(to_char(e.hiredate,'ddmm')||(extract(year from sysdate)+1),'ddmmyyyy') between trunc(sysdate) and sysdate+30

    kovac.dalibor@gmail.com

    ReplyDelete
  13. --Get all the people in the next 15 days including today
    SELECT * FROM scott.emp WHERE TRUNC(hiredate) BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 15 ORDER BY hiredate;

    --Get all the people in the next 15 days excluding today

    SELECT * FROM scott.emp WHERE TRUNC(hiredate) BETWEEN TRUNC(SYSDATE) + 1 AND TRUNC(SYSDATE) + 15 ORDER BY hiredate;

    ReplyDelete
  14. Create a table called integers:
    CREATE TABLE integers
    ( i INTEGER NOT NULL PRIMARY KEY );

    Load the values as follows:
    INSERT INTO integers (i) VALUES (0);
    INSERT INTO integers (i) VALUES (1);
    INSERT INTO integers (i) VALUES (2);
    INSERT INTO integers (i) VALUES (3);
    INSERT INTO integers (i) VALUES (4);
    INSERT INTO integers (i) VALUES (5);
    INSERT INTO integers (i) VALUES (6);
    INSERT INTO integers (i) VALUES (7);
    INSERT INTO integers (i) VALUES (8);
    INSERT INTO integers (i) VALUES (9);

    Now, using the above created table,
    we can retrieve the needed data as given below:

    select e.* from (SELECT to_char(sysdate
    + 10*t.i+u.i,'mm-dd') AS next_30_dates
    FROM integers u
    CROSS
    JOIN integers t
    WHERE 10*t.i+u.i BETWEEN 0 AND 29
    ) date_range, emp e
    where to_char(e.hiredate,'mm-dd') = date_range.next_30_dates;

    This makes use of the cross join effect to get a range of dates(here using only the 'mm-dd' format) for the next 30 days and then map the same with the emp data to get the desired result.

    ReplyDelete
  15. Hi Martin,

    here is my solution:
    SELECT
    tmp.empno
    ,tmp.ename
    ,tmp.job
    ,tmp.hiredate
    ,tmp.hiresary
    FROM (SELECT
    tb.empno
    ,tb.ename
    ,tb.job
    ,tb.hiredate
    ,TRUNC(SYSDATE) AS actual_date
    ,TO_DATE(TO_CHAR(SYSDATE
    ,'YYYY'
    )
    || TO_CHAR(tb.hiredate
    ,'MMDD'
    )
    ,'YYYYMMDD'
    ) AS hiresary
    FROM emp tb
    WHERE 0 = 0
    ) tmp
    WHERE 0 = 0
    AND tmp.hiresary BETWEEN tmp.actual_date
    AND tmp.actual_date + 30
    ORDER BY tmp.hiresary ASC
    ,tmp.hiredate ASC
    ;

    ReplyDelete
  16. Hi,

    Here is my script, it has today as day number 1.

    SELECT ename, hiredate
    FROM emp
    WHERE to_date(TO_CHAR(hiredate, 'dd-mm') ||'-2013', 'dd-mm-yyyy')
    BETWEEN to_date(TO_CHAR(sysdate, 'dd-mm') ||'-2013', 'dd-mm-yyyy')
    AND to_date(TO_CHAR(sysdate + 29, 'dd-mm') ||'-2013', 'dd-mm-yyyy')

    Regards,
    Ingimundur K. Gudmundsson

    ReplyDelete
  17. select *
    from emp
    where to_date(to_char(hiredate,'ddmm')||to_char(sysdate,'yyyy'),'ddmmyyyy')
    between trunc(sysdate) and trunc(sysdate)+30
    order by to_date(to_char(hiredate,'ddmm')||to_char(sysdate,'yyyy'),'ddmmyyyy')

    ReplyDelete
  18. Hello Martin,

    Here are two solutions, with a substitutable variable &N
    for choosing the number of days ( 30 for the problem presented )

    SELECT e.empno,
    e.ename,
    e.hiredate,
    d.myday,
    EXTRACT(YEAR FROM (d.myday - e.hiredate) YEAR(3) TO MONTH) AS hire_age
    FROM
    emp e,
    ( SELECT TRUNC(SYSDATE) + ROWNUM - 1 AS myday
    FROM dual
    CONNECT BY LEVEL <= &N ) d
    WHERE
    TO_CHAR(d.myday,'MMDD') = TO_CHAR(e.hiredate,'MMDD')
    /


    If however, we want to be fair towards somebody who was hired on Feb 29 and celebrate him however on Feb 28,
    then the following solution will accomplish this:

    SELECT e.empno,
    e.ename,
    e.hiredate,
    ADD_MONTHS(e.hiredate, 12 * y.num_years) AS myday,
    y.num_years AS hire_age
    FROM
    emp e,
    ( SELECT ROWNUM num_years
    FROM dual
    CONNECT BY LEVEL <= 100 ) y
    WHERE
    ADD_MONTHS(e.hiredate, 12 * y.num_years) BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + &N - 1
    /


    Thanks a lot & Best Regards,

    Iudith Mentzel
    ZIM Integrated Shipping Services Ltd.
    Haifa, Israel

    ReplyDelete
    Replies
    1. Congrats on winning the contest! Please send me an email with your contact information so that I can pass it along to Red Gate.

      Delete
    2. Hello Martin,

      This was a surprise for me, there were indeed many interesting solutions submitted.
      congratulations to all the participants :) :)

      Oracle is indeed tricky when dealing with Feb 29, so for being strictly correct,
      it looks that for very specific SYSDATE dates and hiredates of Feb 28 or Feb 29,
      probably solutions that use MONTHS_BETWEEN will work correctly,
      while solutions using ADD_MONTHS will not, and this is due to the specific end-of-month logic
      of those two functions.

      My full contact info is:

      Mentzel Iudith
      ZIM Integrated Shipping Services Ltd.
      Haifa, Israel

      e-mail: mentzel.iudith@il.zim.com

      Delete
  19. I already did one fairly simple solution - but it requires full table scan. So here is a second solution that allows index use:


    create or replace function fixdate(
    p_date in date
    , p_year in varchar2
    )
    return date deterministic
    is
    begin
    return to_date(p_year||to_char(p_date,'MMDD'),'YYYYMMDD');
    end fixdate;
    /

    create index emp_fixhiredate on emp (
    fixdate(hiredate,'2000')
    )
    /

    select empno
    , ename
    , hiredate
    from emp
    where fixdate(hiredate,'2000') between fixdate(sysdate,'2000')
    and fixdate(sysdate,'2000') + 30
    or fixdate(hiredate,'2000') between fixdate(sysdate,'1999')
    and fixdate(sysdate,'1999') + 30
    order by fixdate(hiredate,'2000')
    /


    The two different "between" clauses are for cases where we "wrap around" years. Try for example with sysdate=2013-12-30 :-)

    ReplyDelete
  20. Hello Martin again,

    In continuation of my previous post, here is yet another variant of my first solution above,
    that will also cover the case of Feb 29 :

    SELECT e.empno,
    e.ename,
    e.hiredate,
    d.myday,
    EXTRACT(YEAR FROM (d.myday - e.hiredate) YEAR(3) TO MONTH) AS hire_age
    FROM
    emp e,
    ( SELECT TRUNC(SYSDATE) + ROWNUM - 1 AS myday
    FROM dual
    CONNECT BY LEVEL <= &N ) d
    WHERE
    MOD( MONTHS_BETWEEN(d.myday, e.hiredate), 12) = 0
    /


    Thanks again & Best Regards,

    Iudith Mentzel
    ZIM Integrated Shipping Services Ltd.
    Haifa, Israel

    ReplyDelete
  21. It will certainly not be the most beautifull solution, I guess, but here it is:
    You can add another where clause to limit the employees to be the ones you're the manager of by adding: and emp.mgr = .

    select emp.ename
    , emp.hiredate
    , trunc(sysdate) -- test purpose
    , to_date(to_char(emp.hiredate,'DD-MON')||'-'||to_char(sysdate,'YYYY')) -- test purpose
    from emp
    where to_date(to_char(emp.hiredate,'DD-MON')||'-'||to_char(sysdate,'YYYY')) - trunc(sysdate) between 0 and 30

    Kind regards, Marga

    ReplyDelete
  22. select ename, hiredate
    from gbb_emp e
    where to_char(hiredate, 'MM/DD') between to_char(sysdate, 'MM/DD') and to_char(sysdate+30, 'MM/DD')

    ReplyDelete
  23. oops, both of my submissions yesterday had end-of-year bugs in them. I think these should fix both


    SELECT *
    FROM (SELECT emp.*,
    TRUNC(SYSDATE) today,
    CASE
    -- person was hired on a leap-day, but this is not a leap year
    -- then treat the person as having been hired on Mar 1
    WHEN TO_CHAR(hiredate, 'mmdd') = '0229'
    AND TO_CHAR(TRUNC(SYSDATE, 'yyyy') + 59, 'mmdd') != '0229'
    THEN
    -- if you want to treat leap-day as Feb 28
    -- TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || '0228', 'yyyymmdd')
    TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || '0301', 'yyyymmdd')
    ELSE
    TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || TO_CHAR(hiredate, 'mmdd'), 'yyyymmdd')
    END
    birthdaythisyear,
    CASE
    -- person was hired on a leap-day, but this is not a leap year
    -- then treat the person as having been hired on Mar 1
    WHEN TO_CHAR(hiredate, 'mmdd') = '0229'
    AND TO_CHAR(TRUNC(SYSDATE, 'yyyy') + 59, 'mmdd') != '0229'
    THEN
    -- if you want to treat leap-day as Feb 28
    -- TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || '0228', 'yyyymmdd')
    TO_DATE((TO_CHAR(SYSDATE, 'yyyy') + 1) || '0301', 'yyyymmdd')
    ELSE
    TO_DATE((TO_CHAR(SYSDATE, 'yyyy') + 1) || TO_CHAR(hiredate, 'mmdd'),
    'yyyymmdd'
    )
    END
    birthdaynextyear
    FROM scott.emp)
    WHERE birthdaythisyear BETWEEN today AND today + 30
    OR birthdaynextyear BETWEEN today AND today + 30;


    SELECT x.*
    FROM (SELECT TRUNC(SYSDATE) today,
    emp.*,
    TO_CHAR(SYSDATE, 'yyyy') - TO_CHAR(hiredate, 'yyyy') years
    FROM scott.emp) x
    WHERE ADD_MONTHS(hiredate, 12 * years) BETWEEN today AND today + 30
    OR ADD_MONTHS(hiredate, 12 * years + 12) BETWEEN today AND today + 30
    ORDER BY hiredate;

    ReplyDelete
  24. SELECT ename, next_bday
    FROM (
    -- Determine next birthday
    SELECT ename, hiredate
    , CASE WHEN cy > TRUNC( SYSDATE ) THEN cy ELSE ny END next_bday
    FROM (
    -- Calculate birthdays in current and next years
    SELECT ename, hiredate
    , ADD_MONTHS( hiredate, ( EXTRACT( YEAR FROM SYSDATE ) - EXTRACT( YEAR FROM hiredate ) ) * 12 ) cy
    , ADD_MONTHS( hiredate, ( EXTRACT( YEAR FROM SYSDATE ) - EXTRACT( YEAR FROM hiredate ) + 1 ) * 12 ) ny
    FROM emp
    )
    ORDER BY 3
    )
    WHERE ROWNUM <= 15 ;

    ReplyDelete
  25. Martin,

    there is no solution to this problem, because the default EMP table contains no birthday.

    Since this answer doesn't really satisfies me, I will assume for the rest of this comment, that the column HIREDATE contains the birthday of the employee. Furthermore, I read "who's birthday is coming up in the next 15 days" as "who's birthday is tomorrow or the day after tomorrow or ..." but not today.

    Under these assumptions,
    select empno, ename from scott.emp where trunc(hiredate) between trunc(sysdate+1) and trunc(sysdate+15);
    will answer the question

    Matthias

    ReplyDelete
  26. There are two simple solutions (I used the first solution, the second would have worked well):
    1. Use TRUNC(MONTHS_BETWEEN(SYSDATE, emp.birthdate) / 12), 0). Determinate the difference between employee's age at the begin and the end of interval.
    2. Extract the year value from sysdate and sysdate + 30 and replace it in birthdate. Replace birthdate november, 29 with november, 28 if it necessary. Check that this values fall within the interval of 30 days from sysdate.

    P.S. 365.25 - incorrect solution.

    With best regards, Anton Sibiryakov.

    ReplyDelete
  27. SELECT v.AnniversaryDate,
    v.DeptNo,
    v.EName,
    v.HireDate
    FROM (SELECT ADD_MONTHS(TRUNC(E.HireDate), 12 * (CurYear - TO_NUMBER(TO_CHAR(e.HireDate, 'YYYY')))) AnniversaryDate, -- for leap years
    e.DeptNo,
    e.EName,
    e.HireDate,
    Today
    FROM emp e,
    (SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) CurYear,
    TRUNC(SYSDATE) Today
    FROM DUAL
    ) y
    ) v
    WHERE v.AnniversaryDate BETWEEN Today AND Today + 29
    ORDER BY v.AnniversaryDate, v.DeptNo, v.EName
    /

    ReplyDelete
  28. SELECT *
    FROM emp
    WHERE to_date(to_char(SYSDATE - 20, 'yyyy') ||
    CASE to_char(hiredate, 'ddmm')
    WHEN '2902' THEN
    CASE
    WHEN MOD(to_number(to_char(SYSDATE, 'yyyy')), 400) = 0 THEN
    to_char(hiredate, 'ddmm')
    WHEN MOD(to_number(to_char(SYSDATE, 'yyyy')),4) = 0 THEN
    to_char(hiredate, 'ddmm')
    ELSE
    to_char(hiredate - 1, 'ddmm')
    END
    ELSE
    to_char(hiredate, 'ddmm')
    END, 'yyyyddmm') BETWEEN trunc(SYSDATE) AND trunc(SYSDATE) + 30

    ReplyDelete
  29. Maybe I didn't understand the question, because It's seem so simple. Given sysdate=date'1982-02-19', the employees who's anniversary hire date is in the next 30 days is:

    select * from emp where hiredate between add_months(date'1982-02-19',-12) and add_months(date'1982-02-19',-12)+30 order by hiredate;
    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    ----- ----- -------- ----- ------------------- ----- ---- ------
    7499 ALLEN SALESMAN 7698 20/02/1981 00:00:00 1600 300 30
    7521 WARD SALESMAN 7698 22/02/1981 00:00:00 1250 500 30

    2 rows selected.

    ReplyDelete
  30. Hello,

    First of all, I would like to say that, the year parameter have to be ignored in this case. Because, we are not interested in year. So, the important thing is day and month. More importantly, when sysdate corresponds to december, the special case is arisen. Due to the year is ignored. When 30 days will be added to the sysdate. Sysdate will become to the beginning of the year. So, I have to take into account this special case.

    Here is the query:

    select * from emp where
    (to_date(to_char(sysdate, 'DDMM'), 'DDMM') > to_date(to_char(sysdate+30, 'DDMM'), 'DDMM') and
    ((to_date(to_char((hiredate), 'DDMM'), 'DDMM') < to_date(to_char(sysdate+30, 'DDMM'), 'DDMM') and
    to_date(to_char((hiredate), 'DDMM'), 'DDMM') > to_date('0101', 'DDMM'))
    or
    (to_date(to_char((hiredate), 'DDMM'), 'DDMM') > to_date(to_char(sysdate, 'DDMM'), 'DDMM') and
    to_date(to_char((hiredate), 'DDMM'), 'DDMM') < to_date('3112', 'DDMM')))) or
    to_date(to_char(hiredate, 'DDMM'), 'DDMM') BETWEEN to_date(to_char(sysdate, 'DDMM'), 'DDMM') AND to_date(to_char(sysdate+30, 'DDMM'), 'DDMM');

    Also, the test script has been provided in order to be sure that the query works correctly for every period of the year.

    --test
    set serveroutput on;
    declare

    my_date date := '10/01/2013';
    stmt varchar2(50);
    cursor my_cur is
    select * from emp where
    (to_date(to_char(sysdate, 'DDMM'), 'DDMM') > to_date(to_char(sysdate+30, 'DDMM'), 'DDMM') and
    ((to_date(to_char((hiredate), 'DDMM'), 'DDMM') < to_date(to_char(sysdate+30, 'DDMM'), 'DDMM') and
    to_date(to_char((hiredate), 'DDMM'), 'DDMM') > to_date('0101', 'DDMM'))
    or
    (to_date(to_char((hiredate), 'DDMM'), 'DDMM') > to_date(to_char(sysdate, 'DDMM'), 'DDMM') and
    to_date(to_char((hiredate), 'DDMM'), 'DDMM') < to_date('3112', 'DDMM')))) or
    to_date(to_char(hiredate, 'DDMM'), 'DDMM') BETWEEN to_date(to_char(sysdate, 'DDMM'), 'DDMM') AND to_date(to_char(sysdate+30, 'DDMM'), 'DDMM');


    begin

    for rec in 1..13
    loop
    stmt := 'alter system set fixed_date = '''||my_date||'''';
    execute immediate stmt;
    dbms_output.put_line('For sysdate: '||sysdate);
    for rec2 in my_cur
    loop
    dbms_output.put_line(rec2.empno||' '||rec2.ename||' '||rec2.job||' '||rec2.mgr||' '||rec2.hiredate||' '||rec2.sal);
    end loop;
    dbms_output.put_line(null);
    my_date := my_date + 30;
    end loop;

    end;




    Warm Regards,
    Caglar Polat (From Istanbul, Turkey)

    ReplyDelete
  31. unless i am missing something , that was really straight forward...

    SELECT e.ename,
    e.hiredate,
    to_number(to_char(SYSDATE , 'yyyy')) - to_number(to_char(e.hiredate , 'yyyy')) years_service
    FROM emp e
    WHERE to_char(e.hiredate ,'mmdd')
    BETWEEN to_char(SYSDATE,'mmdd' )
    AND to_char(( SYSDATE+30 ),'mmdd' )

    ReplyDelete
  32. select * from emp,
    (
    select TRUNC(SYSDATE)+level-1 AS dates
    from dual
    connect by level <= 15
    )aniversery
    where to_number(to_char(hiredate,'MM')||to_char(hiredate,'DD')) = to_number(to_char(aniversery.dates,'MM')||to_char(aniversery.dates,'DD'))

    Regards
    Intro

    ReplyDelete
  33. select * from emp,
    (
    select TRUNC(SYSDATE)+level-1 AS dates
    from dual
    connect by level <= 15
    )aniversery
    where to_number(to_char(hiredate,'MM')||to_char(hiredate,'DD')) = to_number(to_char(aniversery.dates,'MM')||to_char(aniversery.dates,'DD'))

    Regards
    Intro

    ReplyDelete
  34. on a second thought ...
    SELECT e.ename, e.hiredate
    FROM emp e
    WHERE
    to_char(e.hiredate ,'mmdd') BETWEEN to_char(SYSDATE,'mmdd' ) AND to_char(( SYSDATE+30 ),'mmdd' )
    OR
    (
    trim(to_char((sysdate + 30), 'month')) = 'january' AND
    to_char(e.hiredate ,'mmdd') BETWEEN '0101' AND to_char(( SYSDATE+30 ),'mmdd' )
    )

    ReplyDelete
  35. Hello Martin,

    the problem is to find the next anniversary. So I simply add the difference of whole years between hire_date and SYSDATE (mind the boundary of sysdate = anniversary!)


    WITH anniversary AS (
    SELECT employees.*
    ,ADD_MONTHS (
    hire_date
    ,(FLOOR (
    MONTHS_BETWEEN (TRUNC(SYSDATE) - 1, hire_date) / 12
    )
    + 1)
    * 12
    ) next_anniversary
    FROM employees
    )
    SELECT *
    FROM anniversary
    WHERE next_anniversary - TRUNC(SYSDATE) <= 30
    ORDER BY next_anniversary

    I had to test against the hr.employee table, because at the moment I work with a low (really low) bandwidth connection and the link to the emp script is still loading (more then 30 min now) while I finished the statement.

    BTW: don't put me into the lottery, because I have no use for the Red Gate tool.

    Regards
    Marcus

    P.S.: Sorry, don't know how to add correct tags to preserve code formatting

    ReplyDelete
  36. WITH days AS (SELECT LEVEL-1 NUM
    FROM dual
    CONNECT BY LEVEL <= 30)
    SELECT *
    FROM emp
    ,days
    WHERE REMAINDER(MONTHS_BETWEEN(TRUNC(hiredate)
    ,TRUNC(SYSDATE) + days.num) / 12
    , 1) = 0;

    ReplyDelete
  37. On the first look:

    select *
    from emp
    where add_months(hiredate,12*(to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy')) between trunc(sysdate) and trunc(sysdate) + 30

    But it doesn't look too good...

    ReplyDelete
  38. My answer is as follows:
    select emp.* from emp
    Inner Join ( select rownum-1 as rn from all_objects where rownum<=31 ) on mod(months_between(sysdate+rn,hiredate),12)=0

    ReplyDelete
  39. SELECT *
    FROM emp
    WHERE to_date(to_char(hiredate,'ddmm')||to_char(sysdate,'yyyy'),'ddmmyyyy') BETWEEN trunc(sysdate) AND trunc(sysdate) + 30

    ReplyDelete
  40. Sorry my first post was too soon:-)

    SELECT *
    FROM emp
    WHERE to_date(to_char(hiredate,'ddmm')||to_char(sysdate,'yyyy'),'ddmmyyyy') BETWEEN trunc(sysdate) AND trunc(sysdate) + 30
    OR to_date(to_char(hiredate,'ddmm')||to_char(to_number(to_char(sysdate,'yyyy'))+1),'ddmmyyyy') BETWEEN trunc(sysdate) AND trunc(sysdate) + 30

    ReplyDelete
  41. Hi Martin,
    here is my solution. The most complicated thing was to consider the leap-year. The constant of 59 is the 28.02.

    select * from (
    select emp.*,
    to_char(trunc(ADD_MONTHS(sysdate,12),'YYYY')-1,'DDD')-to_char(trunc(ADD_MONTHS(HIREDATE,12),'YYYY')-1,'DDD') leap_correct,
    to_char(HIREDATE,'DDD') hireday,
    to_char(sysdate,'DDD') sysday
    from emp)
    where hireday-sysday+case when hireday > 59 or hireday < sysday then leap_correct else 0 end+ case when hireday < sysday then 365 else 0 end BETWEEN 0 and 30

    The result is the anniversary hire date between today and the next 30 days.

    -di.ko-

    ReplyDelete
  42. Depending on what floats your boat, eh?
    include trunc to include "today"

    select e.empno, ename, hiredate
    from emp e
    where to_date(to_char(sysdate,'YYYY')||to_char(hiredate,'MMDD'),'YYYYMMDD')-trunc(sysdate) between 0 and 30;

    select * from (
    select e.empno, ename, hiredate
    ,to_date(to_char(sysdate,'YYYY')||to_char(hiredate,'MMDD'),'YYYYMMDD') anniversary
    from emp e)
    where anniversary >= trunc(sysdate)
    and anniversary < sysdate + NUMTODSINTERVAL(30, 'day');

    ReplyDelete
  43. Here is a simple solution

    select name, birthdate
    from person
    where birthdate between trunc(sysdate) and trunc(sysdate+15)
    order by birthdate

    Regards
    Fabrice Perotto

    ReplyDelete
  44. Not the most elegant solution but I believe it gets the job done:
    WITH e AS
    (SELECT
    to_date(to_char(sysdate,'YYYY') || to_char(hiredate,'MMDD'),'YYYYMMDD') current_year_anniversary,
    emp.*
    FROM emp
    )
    SELECT
    empno,
    ename,
    next_anniversary_date
    FROM
    (
    SELECT
    CASE
    WHEN e.current_year_anniversary < trunc(SYSDATE) THEN
    add_months(e.current_year_anniversary,12)
    ELSE
    e.current_year_anniversary
    END next_anniversary_date,
    e.*
    FROM e
    )
    WHERE
    next_anniversary_date - trunc(SYSDATE) <= 30;

    ReplyDelete
  45. Martin,
    Your comment about it not being as easy as it looks intrigues me. I generated a quick solution and used your fixed_date hint to test different scenarios - thanks! Much easier than modifying dates to accomplish the same thing.

    Here's my solution:
    SELECT ENAME,
    TO_DATE(TO_CHAR(HIREDATE,'DD-MON')||'-'||TO_CHAR(SYSDATE,'YYYY')) ANNIV,
    TRUNC(SYSDATE)+30 NEXT30
    FROM EMP
    WHERE
    TO_DATE(TO_CHAR(HIREDATE,'DD-MON')||'-'||TO_CHAR(SYSDATE,'YYYY'))
    BETWEEN TRUNC(SYSDATE)+1 AND TRUNC(SYSDATE)+30
    ORDER BY 2;

    ReplyDelete
  46. This SQL Works by bringing the Hire Date up to the next anniversary via the add_months, treating the current month as a reference point. This means that the search-window will be reliable up to a max. of 11 months.

    Used the with-clause just so I could quickly alter the reference date and search window.

    WITH d AS
    (SELECT trunc(sysdate) focus_date
    ,15 search_days
    FROM dual)
    SELECT d.focus_date
    ,emp.*
    FROM emp
    ,d
    WHERE add_months
    (hiredate
    ,12*
    (EXTRACT (YEAR FROM d.focus_date)
    -EXTRACT (YEAR FROM hiredate)
    + CASE
    WHEN EXTRACT (MONTH FROM d.focus_date)
    > EXTRACT (MONTH FROM hiredate)
    THEN 1
    ELSE 0
    END
    )
    )
    BETWEEN trunc(d.focus_date)
    AND trunc(d.focus_date)+d.search_days

    ReplyDelete
  47. Hi Martin,
    I’ve tried to use your emp table (with “hiredate” instead of birthday) and I’ve edited a bit the dates.
    Here is my attempt:
    select e.ename, e.hiredate as "instead-of-birthday"
    from emp e
    where extract (month from e.hiredate) = extract(month from sysdate)
    and extract (day from e.hiredate) between extract(day from sysdate) and extract(day from sysdate + 15)
    My input to sqlfiddle was like this:
    CREATE TABLE EMP(EMPNO NUMBER(4) NOT NULL,ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE,SAL NUMBER(7, 2),COMM NUMBER(7, 2),DEPTNO NUMBER(2))
    /
    INSERT INTO EMP VALUES
    (7369, 'SMITH', 'CLERK', 7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20)
    /
    INSERT INTO EMP VALUES
    (7499, 'ALLEN', 'SALESMAN', 7698,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30)
    /
    INSERT INTO EMP VALUES
    (7521, 'WARD', 'SALESMAN', 7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30)
    /
    INSERT INTO EMP VALUES(7566, 'JONES', 'MANAGER', 7839,TO_DATE('12-MAR-1981', 'DD-MON-YYYY'), 2975, NULL, 20)
    /
    INSERT INTO EMP VALUES(7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30)
    /
    INSERT INTO EMP VALUES(7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('14-MAR-1981', 'DD-MON-YYYY'), 2850, NULL, 30)
    /
    INSERT INTO EMP VALUES(7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10)
    /
    INSERT INTO EMP VALUES(7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('19-MAR-1982', 'DD-MON-YYYY'), 3000, NULL, 20)
    /
    INSERT INTO EMP VALUES(7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10)
    /
    INSERT INTO EMP VALUES(7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30)
    /
    INSERT INTO EMP VALUES(7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20)
    /
    INSERT INTO EMP VALUES(7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30)
    /
    INSERT INTO EMP VALUES(7902, 'FORD', 'ANALYST', 7566,TO_DATE('23-MAR-1981', 'DD-MON-YYYY'), 3000, NULL, 20)
    /
    INSERT INTO EMP VALUES(7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10)
    /
    Commit
    /
    And here are the results:
    ENAME INSTEAD-OF-BIRTHDAY
    BLAKE March, 14 1981 00:00:00+0000
    SCOTT March, 19 1982 00:00:00+0000
    FORD March, 23 1981 00:00:00+0000

    Kind Regards Alexander
    alexander.andris@gmail.com

    ReplyDelete
  48. SELECT a.empno, a.ename, a.hiredate

    FROM employee a

    WHERE (TO_CHAR(a.hiredate, 'DDD') - TO_CHAR(SYSDATE, 'DDD') between 0 and 29)

    ReplyDelete
  49. SELECT a.empno, a.ename, a.hiredate

    FROM employee a

    WHERE (TO_CHAR(a.hiredate, 'DDD') - TO_CHAR(SYSDATE, 'DDD') between 0 and 29) OR

    (TO_CHAR(a.hiredate, 'DDD') - TO_CHAR(SYSDATE, 'DDD') < 29 - 365) -- for next year

    ReplyDelete
  50. Hi Martin,

    my second attempt is:

    with dte as
    ( select extract(month from sysdate) as crt_mth
    , extract(day from sysdate) as crt_day
    from dual)
    select e.ename, e.hiredate as "instead-of-birthday"
    from emp e, dte
    where extract (month from e.hiredate) = dte.crt_mth
    and extract (day from e.hiredate) between dte.crt_day and (dte.crt_day + 15)

    Kind Regards

    Alexander,

    ReplyDelete
  51. where
    trunc(months_between(sysdate + 30, birthday)/12) =
    trunc(months_between(sysdate + 0, birthday)/12)

    -- I'm not contesting :) It's my solution from
    -- http://www.sql.ru/forum/actualthread.aspx?tid=735298#8348493

    ReplyDelete
    Replies
    1. of cause, inequality :(

      trunc(months_between(sysdate + 30, birthday)/12) != /* different full years */
      trunc(months_between(sysdate + 0, birthday)/12)

      Delete
  52. swart260@xs4all.nlMarch 13, 2013 at 9:46 AM

    select hiredate, ename, dz anniversery
    from
    (select a3.*, case
    when d0 >= sysdate
    then d0
    else add_months(d0,12)-delta1*deel_next
    end dz
    from
    (
    select a2.*, deel_now, deel_next,
    to_date( to_char(sysdate,'yyyy')
    ||'-'
    ||to_char(hiredate-delta,'mm-dd')
    ,'yyyy-mm-dd')+delta*deel_now d0
    from
    (select a1.*,
    case to_char(hiredate,'ddmm')
    when '2902' then 1
    else 0 end delta,
    case to_char(hiredate,'ddmm')
    when '2802' then 1
    else 0 end delta1
    from scott.emp a1
    ) a2
    ,
    ( select
    case mod(year_now,4)
    when 0 then 1
    else 0 end deel_now,
    case mod(year_now+1,4)
    when 0 then 1
    else 0 end deel_next
    from
    ( select to_char(sysdate,'yyyy') year_now
    from dual
    )
    )
    ) a3
    )
    where dz between sysdate and sysdate+30
    order by dz
    /

    ReplyDelete
  53. select e.ename form emp e join (
    select trunc(sysdate)-numtoyminterval(level-1) as sdate,
    trunc(sysdate+15)-numtoyminterval(level-1) as edate,
    from dual connect by level <= (select extract(year from sysdate)
    - extract(year from min(ee.hirdate))
    from emp ee) + 1) a
    where e.hiredate between a.sdate and e.edate

    ReplyDelete
  54. As the HR manager I would use the follow query:

    SELECT *
    SELCT *
    FROM emp
    WHERE FLOOR (MONTHS_BETWEEN (SYSDATE, hiredate) / 12) <
    FLOOR (MONTHS_BETWEEN (SYSDATE + 30, hiredate) / 12);

    Explanation: all employees, who will have their
    annyversary date whithin the next 30 days,
    will then have more WHOLE(that is why FLOOR) service years
    than they have now.
    Actually they will have exact one whole year more, but
    it doesn't matter in this case.

    WBR

    Jewgenij Moldawski

    ReplyDelete
  55. I assume people hired on Feb 29 would have this anniversary every four years, I also assume the list you want is between today and today+30.
    ---- by James Su

    SELECT empno,ename,hiredate
    FROM (SELECT e.*
    ,DECODE(hire_mmdd
    ,'0229'
    ,CASE WHEN TO_CHAR(LAST_DAY(TO_DATE(this_year||'0201','YYYYMMDD')),'DD')='29' THEN this_year||'0229' END
    ,this_year||hire_mmdd
    ) AS this_anni
    ,DECODE(hire_mmdd
    ,'0229'
    ,CASE WHEN TO_CHAR(LAST_DAY(TO_DATE(next_year||'0201','YYYYMMDD')),'DD')='29' THEN next_year||'0229' END
    ,next_year||hire_mmdd
    ) AS next_anni
    FROM (SELECT emp.*,TO_CHAR(HIREDATE,'MMDD') AS hire_mmdd
    ,TO_CHAR(SYSDATE,'YYYY') this_year
    ,TO_CHAR(TO_CHAR(SYSDATE,'YYYY')+1) next_year
    ,TO_CHAR(TRUNC(SYSDATE),'YYYYMMDD') today
    ,TO_CHAR(TRUNC(SYSDATE)+30,'YYYYMMDD') nextday
    FROM emp
    ) e
    )
    WHERE this_anni BETWEEN today AND nextday
    OR next_anni BETWEEN today AND nextday;

    ReplyDelete
  56. Following is a solution, yeilding expected results:

    with inp as
    (
    select to_date('&i', 'DD-Mon-YYYY') dt from dual
    ),
    get_anniv_msg as
    (
    select e.*, dt,
    case
    when to_char(hiredate, 'MMDD') between to_char(trunc(dt), 'MMDD') and to_char(trunc(dt) + 15, 'MMDD')
    then 'Anniversary due within 15 days'
    else 'No Anniversary yet.'
    end ann_msg
    from emp e, inp
    )
    select empno, ename, mgr, hiredate,
    'Anniversary is due in ' || to_char(add_months(hiredate, round(months_between(dt, hiredate))) - dt) || ' days.' ann_msg
    from get_anniv_msg
    where ann_msg = 'Anniversary due within 15 days';

    ReplyDelete
  57. Hi Martin,

    yesterday i wrote you twice but afterwards i've realized that my solution
    was not complete (i was ignoring birthdays from the next month but within 15
    days from now). So I've amended my query, now it looks like this:

    WITH dte AS

    (
    SELECT extract(MONTH FROM sysdate) AS crt_mth

    , extract(DAY FROM sysdate) AS crt_day

    FROM dual
    )

    SELECT e.ename, e.hiredate AS "instead-of-birthday"

    FROM emp e, dte

    WHERE ( EXTRACT (MONTH FROM e.hiredate) = dte.crt_mth AND
    EXTRACT (DAY FROM e.hiredate) BETWEEN dte.crt_day AND (dte.crt_day + 15)
    )
    OR (
    ((EXTRACT (MONTH FROM e.hiredate) = dte.crt_mth + 1
    ) OR (dte.crt_mth=12 and EXTRACT(MONTH FROM e.hiredate) =1 )) AND
    (EXTRACT (DAY FROM LAST_DAY(e.hiredate)) - dte.crt_day + 1 + EXTRACT(DAY FROM e.hiredate) <= 15) )



    or, an equivalent form is:


    WITH dte AS

    (
    SELECT extract(MONTH FROM sysdate) AS crt_mth

    , extract(DAY FROM sysdate) AS crt_day

    FROM dual
    )

    SELECT e.ename, e.hiredate AS "instead-of-birthday"

    FROM emp e, dte

    WHERE ( EXTRACT (MONTH FROM e.hiredate) = dte.crt_mth AND
    EXTRACT (DAY FROM e.hiredate) BETWEEN dte.crt_day AND (dte.crt_day + 15)
    )
    OR (
    ((EXTRACT (MONTH FROM e.hiredate) = dte.crt_mth + 1
    ) OR (dte.crt_mth=12 and EXTRACT(MONTH FROM e.hiredate) =1 )) AND
    (EXTRACT (DAY FROM LAST_DAY(e.hiredate)) - dte.crt_day + EXTRACT(DAY FROM e.hiredate) < 15))


    ( still there's the question if you mean 15 days including today's date or today + NEXT 15 days - because between a and b there are b -a + 1 days but i supposed you mean today + 15 days

    ...)
    I haven't tested this query thoroughly but i hope now it returns correct results.

    Thank You for the puzzle/question.

    Kind Regards

    Alexander, Prague

    ReplyDelete
  58. SQL> select *
    2 from (select e.empno
    3 ,e.ename
    4 ,to_date(to_char(e.hiredate, 'dd.mm.') || to_char(sysdate, 'yyyy'), 'dd.mm.yyyy') d
    5 from emp e)
    6 where d between sysdate and sysdate + interval '30' day
    7 /
    EMPNO ENAME D
    ----- ---------- -----------
    7566 JONES 02.04.2013

    ReplyDelete
  59. Hello!

    I am not sure if my first post was correct enough, so I checked it today and here is my solution. Sorry for this mess.

    select ename
    from emp e join
    (select TRUNC(SYSDATE) - numtoyminterval( level-1, 'YEAR') as sdate
    ,TRUNC(SYSDATE) - numtoyminterval(level-1,'YEAR')+15 as edate
    from dual connect by level <= (select extract(year from SYSDATE) - extract(year from min(ee.hiredate))+1 from emp ee)) a
    on e.hiredate between a.sdate and a.edate
    /

    ReplyDelete
  60. WITH
    Data_SYSDATE AS (
    SELECT To_Char( SYSDATE, 'MMDD') AS NowMMDD,
    To_Char( SYSDATE, 'YYYY') AS ThisYear,
    To_Char( Add_Months( SYSDATE, 12), 'YYYY') AS NextYear
    FROM Dual
    ),
    Data_Employees AS (
    SELECT e.EmpNo AS EmpNo,
    To_Char( e.HireDate, 'MMDD') AS HireMMDD
    FROM Emp e
    ),
    Data_Prepared AS (
    SELECT e.EmpNo AS EmpNo,
    To_Date( (CASE WHEN (e.HireMMDD < s.NowMMDD)
    THEN s.NextYear
    ELSE s.ThisYear END)
    || e.HireMMDD,
    'YYYYMMDD') AS DateToCheck
    FROM Data_SYSDATE s,
    Data_Employees e
    )
    SELECT p.DateToCheck AS "Anniversary date",
    Extract( YEAR FROM ((p.DateToCheck - e.HireDate)
    YEAR(3) TO MONTH)) AS ".th Anniversary",
    e.EName AS "Employee",
    e.Job AS "Job",
    d.DName AS "Department",
    d.Loc AS "Location",
    e.HireDate AS "Hire date"
    FROM Data_Prepared p,
    Emp e,
    Dept d
    WHERE (p.DateToCheck BETWEEN Trunc( SYSDATE) AND Trunc( SYSDATE + 30))
    AND (e.EmpNo = p.EmpNo)
    AND (d.DeptNo = e.DeptNo)
    ORDER BY p.DateToCheck, e.HireDate;

    /*
    my email-address: niels.hecker@mt-ag.com
    */

    ReplyDelete
  61. The tricky situation is if someone was hired on the 29th of Feb one year, or you run the code on Feb 29th (but probably not both). If you try straight year substitutions, you'll get errors.

    I'll opt for this.

    select hiredate, add_months(hiredate, 12* (1+(extract (year from (sysdate - hiredate) year to month))))
    from emp
    where add_months(hiredate, 12* (1+(extract (year from (sysdate - hiredate) year to month)))) between sysdate and sysdate + 30;

    ReplyDelete
  62. Hello Martin,

    I have already submitted the answer, but of course I made a beginner's mistake by not taking into account hire dates that, in regard to sysdate, come next year.

    So here is the correct solution:

    select *
    from emp
    where add_months(hiredate,12*(to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy')+case when to_char(sysdate,'mmdd') > to_char(hiredate,'mmdd') then 1 else 0 end)) between trunc(sysdate) + 1 and trunc(sysdate) + 30

    It looks nasty!

    Here is a little bit better version:

    select *
    from ( select e.*,
    to_char(sysdate, 'yyyy')-to_char(e.hiredate, 'yyyy') years_between,
    case
    when to_char(sysdate, 'mmdd') > to_char(e.hiredate, 'mmdd') then
    1
    else
    0
    end correction
    from emp e)
    where add_months(hiredate, 12*(years_between + correction)) between trunc(sysdate) + 1 and trunc(sysdate) + 30

    This code treats February 29th as "the end of February", so it will be treated as February 28th in common years. If it should have been treated as "the day after February 28th" (that is, as March 1st in common years) then another correction should be made:

    select *
    from ( select e.*,
    to_char(sysdate, 'yyyy')-to_char(e.hiredate, 'yyyy') years_between,
    case
    when to_char(sysdate, 'mmdd') > to_char(e.hiredate, 'mmdd') then
    1
    else
    0
    end correction,
    case
    when to_char(e.hiredate, 'mmdd') = '0229' then
    case
    when to_char(sysdate, 'mmdd') > to_char(e.hiredate, 'mmdd') and
    mod(to_char(sysdate, 'yyyy') + 1,4) = 0 and
    (mod(to_char(sysdate, 'yyyy') + 1,100) != 0 or mod(to_char(sysdate, 'yyyy') + 1,400) = 0) then
    0
    when to_char(sysdate, 'mmdd') <= to_char(e.hiredate, 'mmdd') and
    mod(to_char(sysdate, 'yyyy'),4) = 0 and
    (mod(to_char(sysdate, 'yyyy'),100) != 0 or mod(to_char(sysdate, 'yyyy'),400) = 0) then
    0
    else
    1
    end
    else
    0
    end corr_feb29
    from emp e )
    where add_months(hiredate, 12*(years_between + correction)) + corr_feb29 between trunc(sysdate) + 1 and trunc(sysdate) + 30

    With this correction when sysdate is January 29th employees with the hire date February 29th will not be shown whether the year is leap or it is common.

    ReplyDelete
  63. Hi Martin,

    for most of the case this will work:

    WITH next_days AS
    (
    SELECT TO_CHAR(SYSDATE + level, 'MMDD') dt
    FROM DUAL
    CONNECT BY LEVEL <= 30
    )
    SELECT e.*
    FROM emp e
    JOIN next_days nd
    ON (TO_CHAR(e.hiredate, 'MMDD') = nd.dt);

    However there is an exception to consider. What if the hiredate is on 29-Feb and current year is not leap year?
    In this case the solution becomes more complex. I have assumed that in case an employee is hired on 29-Feb and current year is not a leap year we celebrate the anniversary on 28-Feb.

    Here is the query:
    WITH leap_year AS
    (
    SELECT CASE
    WHEN ( MOD(EXTRACT(YEAR FROM SYSDATE), 4) = 0
    AND MOD(EXTRACT(YEAR FROM SYSDATE), 100) != 0
    )
    OR MOD(EXTRACT(YEAR FROM SYSDATE), 400) = 0
    THEN
    1
    ELSE
    0
    END AS flag
    FROM DUAL
    )
    , next_days AS
    (
    SELECT TO_CHAR(SYSDATE + level, 'MMDD') dt
    FROM DUAL
    CONNECT BY LEVEL <= 30
    )
    SELECT e.*
    FROM emp e
    CROSS JOIN leap_year l
    JOIN next_days nd
    ON ((CASE WHEN l.flag = 0 AND TO_CHAR(e.hiredate, 'MMDD') = '0229'
    THEN '0228'
    ELSE
    TO_CHAR(e.hiredate, 'MMDD')
    END
    ) = nd.dt);

    Regards.
    Alberto Faenza
    afaenza@gmail.com

    ReplyDelete
  64. select ename, hiredate
    from emp
    where to_char(hiredate, 'MMDD') between to_char(sysdate, 'MMDD') and to_char(sysdate+30, 'MMDD')

    ReplyDelete
  65. Hi Martin.

    Here's my effort. Getting the correct answer following a year end required some thought!

    SELECT e.*
    FROM emp e
    WHERE TO_DATE(TO_CHAR(hiredate,'mm/dd') ||
    CASE WHEN TO_CHAR(hiredate,'mm/dd') < TO_CHAR(SYSDATE,'mm/dd')
    THEN extract(year from sysdate) + 1
    ELSE extract(year from sysdate)
    END, 'mm/dd/yyyy')
    BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 30;

    Graham

    ReplyDelete
  66. I have two more solutions without the need to check if the year is leap or common... The problem is really interesting since it can be solved using completely different approaches (the first solution adds months to the hire date, the second solution "injects" day and month from hire date into the appropriate year).

    Both solutions treat February 29 as March 1 in common years. Queries do not check if the sysdate is less than hire date, which is probably a drawback since we do not want to celebrate someone's anniversary in the case of time travel. Or do we?

    select *
    from ( select e.*,
    to_char(sysdate, 'yyyy')-to_char(e.hiredate, 'yyyy') years_between,
    case
    when to_char(sysdate, 'mmdd') > to_char(e.hiredate, 'mmdd') then
    1
    else
    0
    end correction,
    decode(to_char(e.hiredate,'mmdd'),'0229',hiredate-1,hiredate) new_hiredate,
    decode(to_char(e.hiredate,'mmdd'),'0229',1,0) corr_feb29
    from emp e)
    where add_months(new_hiredate, 12*(years_between + correction))+corr_feb29 between trunc(sysdate) + 1 and trunc(sysdate) + 30;

    select *
    from ( select e.*,
    decode(to_char(e.hiredate,'mmdd'),'0229','0228',to_char(e.hiredate,'mmdd')) month_day,
    case
    when to_char(sysdate,'mmdd') > to_char(e.hiredate,'mmdd') then
    to_char(to_char(sysdate,'yyyy')+1)
    else
    to_char(sysdate,'yyyy')
    end year,
    decode(to_char(e.hiredate,'mmdd'),'0229',1,0) corr_feb29
    from emp e)
    where to_date(month_day || year,'mmddyyyy')+corr_feb29 between trunc(sysdate) + 1 and trunc(sysdate) + 30;

    ReplyDelete
  67. Here are three options I came up with in the time I allotted to myself for this quiz.

    select ename, hiredate
    from emp
    where to_char(hiredate, 'MMDD') BETWEEN to_char(SYSDATE,'MMDD') AND to_char(SYSDATE + 30, 'MMDD');


    select ename, hiredate
    from emp
    where trunc(MONTHS_BETWEEN(SYSDATE-1, hiredate)/12) != trunc(MONTHS_BETWEEN(SYSDATE+30, hiredate)/12);


    select ename, hiredate
    from emp
    where to_date(to_char(SYSDATE, 'YYYY') || to_char(hiredate, '-MM-DD'), 'YYYY-MM-DD') BETWEEN TRUNC(SYSDATE) AND SYSDATE + 30;

    ReplyDelete
  68. Hi Martin :-D
    My name is Roberto and my email is munoz.gonzalez.roberto@gmail.com

    Here is my solution,
    thank you very much!

    select empno,
    ename,
    hiredate
    from (
    select empno,
    ename,
    CASE WHEN to_date(to_char(sysdate,'YYYY')||to_char(hiredate,'MM')||to_char(hiredate,'DD'),'YYYYMMDD')<SYSDATE THEN
    to_date(to_char(sysdate,'YYYY')-1||to_char(hiredate,'MM')||to_char(hiredate,'DD'),'YYYYMMDD')
    ELSE
    to_date(to_char(sysdate,'YYYY')||to_char(hiredate,'MM')||to_char(hiredate,'DD'),'YYYYMMDD')
    END refdate,
    hiredate
    from emp
    )
    where refdate between sysdate and sysdate + 30
    /

    ReplyDelete
  69. There are some nice solutions up here.
    But some really scare me!
    I mean: how can one consider that "birthdate" or "hiredate" will ever again be "between trunc(sysdate) and trunc(sysdate + whatever)"
    Can only mean that the solution is considered correct without even running (read testing) it once.

    ReplyDelete
  70. SELECT empno, ename, hiredate
    FROM emp
    WHERE to_date(TO_CHAR(hiredate, 'dd mon')
    ||extract(YEAR FROM sysdate))<= SYSDATE+30
    AND to_date(TO_CHAR(hiredate, 'dd mon')
    ||EXTRACT(YEAR FROM SYSDATE))>= SYSDATE;

    ReplyDelete
  71. My (independent !) solution is like Iudith's but doesn't assume maximum length of service
    select e.ename,e.hiredate,add_months(e.hiredate,12 * years.year) anniversary, years.year
    from emp e,
    (select level year from dual connect by level < months_between(sysdate, (select min(hiredate) from emp)) / 12) years
    where add_months(hiredate, 12 * years.year) between to_date(:today_date) and to_date(:today_date) + :no_days

    Based on the EMP data in the link, setting today_date=01-FEB-2013, no_days=60 returns 3 employees, but with 01_FEB-2012, only 2 are returned

    ReplyDelete
  72. Hello Martin,

    My name is Hamid Talebian and my email is H.Talebian@pz.agro.nl

    I know that I am too late, I misread 15 march as 25 march. Any how this is my solution:

    There are two scenario's for an emlployee with hiredate 29-feb:

    1- Anniversary is 28-feb or 29-feb (anniversary in every years)
    2- Anniversary is allways 29-feb (anniversary only in leap years!!)

    For scenario 1:

    with
    hr_emp as
    (select empno, ename, job, mgr, hiredate, sal, comm, deptno
    , case when to_char(sysdate, 'MMDD') <= to_char(hiredate, 'MMDD') then 0 else 1 end y_ind
    ,to_char(sysdate, 'RRRR') - to_char(hiredate, 'RRRR') years
    -- ,to_char(hiredate, 'MMDD') MM_DD
    from emp)
    , emp_anniv as
    (select empno, ename, job, mgr, hiredate, sal, comm, deptno, y_ind, years
    , case to_char(hiredate, 'MMDD')
    when '0228' then to_date(to_char((to_char(hiredate, 'RRRR') + years + y_ind))||'0228', 'rrrrmmdd')
    else add_months(hiredate, 12 * (y_ind + years))
    end anniversary
    from hr_emp
    )
    select * from emp_anniv
    where anniversary between trunc(sysdate) and trunc(sysdate) +30

    For scenario 2:

    with
    hr_emp as
    (select empno, ename, job, mgr, hiredate, sal, comm, deptno
    , case when to_char(sysdate, 'MMDD') <= to_char(hiredate, 'MMDD') then 0 else 1 end y_ind
    ,to_char(sysdate, 'RRRR') - to_char(hiredate, 'RRRR') years
    -- ,to_char(hiredate, 'MMDD') MM_DD
    from emp)
    , emp_anniv as
    (select empno, ename, job, mgr, hiredate, sal, comm, deptno, y_ind, years
    , case to_char(hiredate, 'MMDD')
    when '0228' then to_date(to_char((to_char(hiredate, 'RRRR') + years + y_ind))||'0228', 'rrrrmmdd')
    else add_months(hiredate, 12 * (y_ind + years))
    end anniversary
    , add_months(hiredate, 12 * (4 * ceil( (y_ind + years) / 4))) anniversary_spec
    from hr_emp
    )
    select * from emp_anniv
    where
    (to_char(hiredate, 'MMDD') = '0229' and anniversary_spec between trunc(sysdate) and trunc(sysdate) +30)
    or
    (to_char(hiredate, 'MMDD') != '0229' and anniversary between trunc(sysdate) and trunc(sysdate) +30)

    Warm regards,

    Hamid Talebian

    ReplyDelete
  73. Hello, Martin!

    When you plan to publish your solution? Looking forward to compare all answers.

    Regards,
    Pavel S.Vorontsov

    ReplyDelete
  74. SELECT ename,
    hiredate,
    TO_DATE(TO_CHAR(hiredate,'MMDD')||TO_CHAR(SYSDATE,'YYYY'),'MMDDYYYY') anniversary
    WHERE TO_DATE(TO_CHAR(hiredate,'MMDD')||TO_CHAR(SYSDATE,'YYYY'),'MMDDYYYY') BETWEEN SYSDATE AND SYSDATE + &no_of_days

    ReplyDelete
  75. Kim Berg Hansen pointed out to me that my query was not as robust as it should be.
    Thanks for that Kim.

    He showed me that it had 2 flaws:
    1. a person hired on the February 28 in a non-leap year would have anniversaries on February 29 in leap years, because of the way add_months works with last-day-of-the-month dates.
    2. It did not list people having a anniversary just after new year if sysdate is just before new year (and the anniversary is in fact within 30 days).

    I addressed both issues, and then found out that I liked the solution Kim build for issue 2 a lot better then my own.
    So, with his permission, I incorporated that in my query (ceil(months_between())).

    Here it is, the updated version:


    DEFINE no_of_days=30

    WITH raw_dates
    AS (SELECT ename
    , hiredate
    , ADD_MONTHS(hiredate
    ,12 * GREATEST (CEIL (MONTHS_BETWEEN (SYSDATE,hiredate)/12),1)
    ) raw_anniversary_date
    FROM emp
    )
    , dates
    AS (SELECT ename
    , hiredate
    , CASE
    WHEN EXTRACT (DAY FROM raw_anniversary_date) > EXTRACT (DAY FROM hiredate)
    THEN raw_anniversary_date - 1
    ELSE raw_anniversary_date
    END anniversary_date
    FROM raw_dates
    )
    SELECT ename
    , hiredate
    , anniversary_date
    , anniversary_date - TRUNC(SYSDATE) "days from now"
    FROM dates
    WHERE (anniversary_date - TRUNC(SYSDATE)) BETWEEN 0 AND &no_of_days
    ;


    explanation:
    Query 'raw_dates' calculates the next anniversary date on or after sysdate
    It does so by first calculating the number of years between hiredate and now (months_between/12), rounding this up (ceil) and then adding his amount of years times 12 months to hiredate.
    If the number of years appears to be 0, hiredate equals today, so next anniversary will be next year. Hence greatest(... , 1)

    Note that at this point a hiredate of 28th of February in a non-leap year is the last day of the month, and so for a sysdate in a leap year will return the 29th of February as raw_anniversary_date.

    To overcome this, query 'dates' takes the result-set of 'raw_dates', and if the day-of-month of raw_anniversary_date is higher then the one from hiredate, raw_anniversary_date is decreased by 1.

    Finally the main query takes the 'dates' result-set and only returns rows for which the anniversary date is between now and 30 days

    ReplyDelete