Find employees above 65

Hi all

I have the following query which finds male employees above 65. This will be sent to managers on a monthly basis.

The things is, i only want people to be sent on this report once. I think my query will identify people over 65 every month. Once this one report has gone out, the manager will no longer need that report.

BIRTH_DATE >= add_months(sysdate, -65*12) -7

The -7 bit, will this prevent managers getting the same report with the same people on a monthly basis?

with CTE_EmployeeInfo AS
(
select
 e.PERSON_REF,
per.sex,
e.EMPLOYEE_NUMBER EMPNO,
per.SURNAME || ', ' || per.FIRST_FORNAME NAME,
per.SURNAME SURNAME,
per.FIRST_FORNAME FORENAME,
per.NI_NO NINO,
per.INITIALS INITS,
per.TITLE TITLE,
per.BIRTH_DATE,
per.KNOWN_AS KNOWNAS,
e.START_DATE EMPSTARTDATE,
e.END_DATE EMPENDDATE,
CGrp.ID CGRPID,
CGrp.LONG_DESC CGRPDESC,
PGrp.ID PGRPID,
PGrp.LONG_DESC PGRPDESC
from D550M e
left join D500M per on e.PERSON_REF = per.PERSON_REF
left join D100M CGrp on e.LEVEL1_PAY_STR_REF = CGrp.REF
left join D100M PGrp on e.PAY_STR_REF = PGrp.REF
join D500M per on e.PERSON_REF = per.PERSON_REF
)
select  
EMPNO,
FORENAME,
SURNAME,
BIRTH_DATE
from CTE_EmployeeInfo  
where
--BIRTH_DATE >= add_months(sysdate, -16*12) 
BIRTH_DATE >= add_months(sysdate, -65*12) -7
AND sex = 'M'

Open in new window


If it doesnt, could someone show me where im going wrong?

Many thanks,
Richiep86Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

sammySeltzerCommented:
That code in itself will give you the result you need but only once.

To have the same result every month, I think you will have to create a job and schedule it to run every month.

I have not done job scheduling in Oracle in long time, I deal exclusively with SQL Server now unfortunately.
0
slightwv (䄆 Netminder) Commented:
Sounds like you are only looing for people that turned 65 the month the report runs.

Try this:
(bdate>=add_months(trunc(sysdate,'MM'),-65*12) and  bdate<add_months(trunc(sysdate,'MM'),(-65*12)+1))
0
Thibault St john Cholmondeley-ffeatherstonehaugh the 2ndCommented:
if someone joins who is already over 65 you will miss them. Should probably include a check on the creation date of the record as well. Then it will return on ages over 65 this month from birthday and ages over 65 if record less than or equal to one month old.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Richiep86Author Commented:
Thanks for the response guys:

@Sammy - This report will be scheduled to run once a month. If this is the case, will the same employees be on the next report when its run next month? Do i need to do anything else other than schedule it to run monthly?

@slight -
(BIRTH_DATE>=add_months(trunc(sysdate,'MM'),-65*12) and  BIRTH_DATE<add_months(trunc(sysdate,'MM'),(-65*12)+1)) 

Open in new window

but this didnt return any results.

@Ors-Ankh-Aten - didnt think of that. Thanks for the heads up.

Rich
0
slightwv (䄆 Netminder) Commented:
>>but this didnt return any results.

Do you have people that will turn 65 this month?
0
slightwv (䄆 Netminder) Commented:
Here is my test that I used.

If I did my math right, only id 2 will turn 65 this month.

drop table tab1 purge;
create table tab1(id number, bdate date);

insert into tab1 values(1,to_date('03/01/1950','MM/DD/YYYY'));
insert into tab1 values(2,to_date('04/15/1950','MM/DD/YYYY'));
insert into tab1 values(3,to_date('04/15/1955','MM/DD/YYYY'));
commit;

select id from tab1 where bdate>=add_months(trunc(sysdate,'MM'),-65*12) and  bdate<add_months(trunc(sysdate,'MM'),(-65*12)+1);

Open in new window

0
Richiep86Author Commented:
Hi Slight


I just put an employee into the system who will be turning 65 in 5 days. DOB = 25/04/1950

Unfortunately your query didnt find him...

Thanks for your help,

Rich
0
slightwv (䄆 Netminder) Commented:
My updated test case and results:
drop table tab1 purge;
create table tab1(id number, bdate date);

insert into tab1 values(1,to_date('03/01/1950','MM/DD/YYYY'));
insert into tab1 values(2,to_date('04/15/1950','MM/DD/YYYY'));
insert into tab1 values(3,to_date('04/15/1955','MM/DD/YYYY'));
insert into tab1 values(4,to_date('04/25/1950','MM/DD/YYYY'));
commit;

select id from tab1 where bdate>=add_months(trunc(sysdate,'MM'),-65*12) and  bdate<add_months(trunc(sysdate,'MM'),(-65*12)+1);

Open in new window



The results:
SQL> select id from tab1 where bdate>=add_months(trunc(sysdate,'MM'),-65*12) and  bdate<add_months(trunc(sysdate,
'MM'),(-65*12)+1);

        ID
----------
         2
         4

Open in new window

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
awking00Information Technology SpecialistCommented:
Slight variation -
select id from tab1 where trunc(add_months(bdate,65*12),'mm') = trunc(sysdate,'mm');
0
slightwv (䄆 Netminder) Commented:
>>Slight variation -

That works unless there is an index on bdate.  Functions on indexed columns disable the use of the index unless the index is a function-based index.
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.