5 years before ORIG_START_DATE

Hi

I know this is pretty simple but this isnt working for me for some reason.

All i want to do is return staff who have been at the company 5 years from their ORIG_START_DATE. I only want this to return people who have been working for 5 years in the month they have been working 5 years.

  AND ADD_MONTHS(D550M.ORIG_START_DATE,5*12)BETWEEN sysdate AND sysdate+31
 

Open in new window


What am i missing?

Thanks
Richiep86Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DavidSenior Oracle Database AdministratorCommented:
A space between ) and BETWEEN, for one thing....
0
Gerwin Jansen, EE MVETopic Advisor Commented:
where ORIG_START_DATE between trunc (sysdate,'MM') - 5*365-1 and trunc(last_day(sysdate)) - 5*365-1
0
DavidSenior Oracle Database AdministratorCommented:
I may be overlooking a correct source, but I don't find any examples allowing the num_months to be calculated.  Try simply, "60".
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
AND D550M.ORIG_START_DATE >= add_months(trunc(sysdate,'mm'),-60)
AND D550M.ORIG_START_DATE < add_months(trunc(sysdate,'mm'),-59)
0
Richiep86Author Commented:
Morning all - thanks for the prompt replies.

I tried this:

AND D550M.ORIG_START_DATE >= add_months(trunc(sysdate,'mm'),-60)
AND D550M.ORIG_START_DATE < add_months(trunc(sysdate,'mm'),-59) 

Open in new window


and

ORIG_START_DATE between trunc (sysdate,'MM') - 5*365-1 and trunc(last_day(sysdate)) - 5*365-1 

Open in new window


but neither worked.

The original start date of the employee is 5 years ago today - 12/05/2010.

any ideas?
0
Richiep86Author Commented:
This worked!

ORIG_START_DATE <= sysdate - interval '5' year
0
Gerwin Jansen, EE MVETopic Advisor Commented:
strange, the <= in what you say is working would mean that employees that are working longer than 5 years would also be in your result. from what I understood this is not what you asked for?
0
Richiep86Author Commented:
I used this:

ORIG_START_DATE >= sysdate - interval '5' year
and
ORIG_START_DATE <= sysdate - interval '6' year
0
sdstuberCommented:
that's not what you asked for though,  that will give you people that have worked for 5 years for an entire year, not for the month they started.

note  I used ADD_MONTHS with -60 intentionally  instead of interval '5' year.

the reason for that choice is interval '5' year won't work on Feb 29  but add_months will.


also I used < instead of <=  for the upper boundary intentionally as well so you don't include one extra day.


if this didn't work:

AND D550M.ORIG_START_DATE >= add_months(trunc(sysdate,'mm'),-60)
AND D550M.ORIG_START_DATE < add_months(trunc(sysdate,'mm'),-59) 

Open in new window


please explain what it did or did not do that you were expecting.
it does what you asked, but maybe not what you intended.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richiep86Author Commented:
I'm out of the office now.

Thus will run on the first of each month, I understand your logic but it didn't find the employees I was looking for.

Your logic seems better, but ad this is run on he first of each month I think it will be ok.

I'll try your example tomorrow and let you kno how I get on.  Thanks alot.

Rich
0
Richiep86Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 250 points for Gerwin Jansen's comment #a40772195
Assisted answer: 0 points for Richiep86's comment #a40772227
Assisted answer: 250 points for sdstuber's comment #a40772255

for the following reason:

This helped me find the solution i was looking for! Thanks
0
Richiep86Author Commented:
Help me find me solution
0
sdstuberCommented:
I got all of the points.  Did you want to do a split or did you change your mind?

we can reopen the question and close again to split with Gerwin Jansen's post if you want
0
Gerwin Jansen, EE MVETopic Advisor Commented:
I'm seeing a split? It's OK with me ;)
0
Richiep86Author Commented:
Do I need to do anything lads?
Thanks to all that helped.
Very grateful.
0
sdstuberCommented:
hmmm I don't see a split.

I see a comment where a split was intended,  but then the actual close is all for me.
0
Gerwin Jansen, EE MVETopic Advisor Commented:
>> Do I need to do anything lads?
If you meant to split points then just say so, I can reopen this question so you can close differently.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.