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

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

All Courses

From novice to tech pro — start learning today.