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.
SYSDATEfor today's date. I'll just alter the
FIXED_DATEsetting in oracle to set the
SYSDATEvalue for my testing.
- Write your query for the default
EMPtable. If you don't have the
EMPtable 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.