One thing that most people got wrong was what happened if the hire date was on Feb 29th (during a leap year) while the current year did not have a leap year. I could do a search online to find some recent leap years, instead I decided to wrote a query to find recent leap years.
-- Find past leap years -- https://forums.oracle.com/forums/thread.jspa?threadID=1128019 select yr, case when mod(yr,400) = 0 or mod(yr,4) = 0then 'Leap Year' else null end leap from ( select extract (year from add_months(sysdate, -8*12)) + level - 1 yr from dual connect by level <= 8 );Test Data
Instead of testing against the main sample EMP table I recreated the EMP table with all the dates in a given leap year (2008). I kept the same structure as the EMP table so that it would be easy to test your solutions against.
drop table emp; -- create mock emp table with all the dates (including leap year create table emp as select level empno, level ename, level job, level mgr, to_date('01-01-2008', 'DD-MM-YYYY') + level - 1 hiredate, level sal, level comm, level deptno from dual connect by level <= 366;Picking the winner and testing solution:
Since we had 63 entries I wasn't going to test them all. Instead I decided to randomly pick solutions (based on the entry number) and validate their solution. If they were correct I'd mark them as a winner. If not, I'd move on to the next entry until I found 2 winners.
Here's how I picked the random entries:
-- Chose random winners select trunc(dbms_random.value(1,63)) from dual;To test the solutions I set my current SYSDATE to 1-Feb-2013 so that it would have to encounter people that were hired on 29-Feb (even though 29-Feb doesn't exist in 2013).
ALTER SYSTEM SET fixed_date='2013-02-01-14:10:00';Winners
The winners are Erik van Roon (aka "Belly") and Iudith Mentzel. Please email me (my email address is top right corner of blog) and I will forward your information to Red Gate so that you can claim your prize.
Thanks for all the entries and thanks again to Red Gate for offering up the prizes (licenses to their new product: Source Control for Oracle). If you haven't already seen what Source Control for Oracle is or want to download a free trial I encourage you to check it out.