Pages

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:

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.

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;

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.

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

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 logicof those two functions.

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 :-)

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

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.

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.

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.

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.

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.

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.

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 15days from now). So I've amended my query, now it looks like this:

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:

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.

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?

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.

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())).

explanation:Query 'raw_dates' calculates the next anniversary date on or after sysdateIt 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