asked on

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

David VanZandt

A space between ) and BETWEEN, for one thing....

Gerwin Jansen

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

David VanZandt

I may be overlooking a correct source, but I don't find any examples allowing the num_months to be calculated. Try simply, "60".

Sean Stuber

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

Richiep86

ASKER

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?

Richiep86

ASKER

This worked!

ORIG_START_DATE <= sysdate - interval '5' year

Gerwin Jansen

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?

Richiep86

ASKER

I used this:

ORIG_START_DATE >= sysdate - interval '5' year
and
ORIG_START_DATE <= sysdate - interval '6' year

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Richiep86

ASKER

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

Richiep86

ASKER

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

Richiep86

ASKER

Help me find me solution

Sean Stuber

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

Gerwin Jansen

I'm seeing a split? It's OK with me ;)

Richiep86

ASKER

Do I need to do anything lads?
Thanks to all that helped.
Very grateful.

Sean Stuber

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.

Gerwin Jansen

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