Link to home
Start Free TrialLog in
Avatar of Richiep86
Richiep86

asked on

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,
Avatar of sammySeltzer
sammySeltzer
Flag of United States of America image

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.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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))
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.
Avatar of Richiep86

ASKER

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
>>but this didnt return any results.

Do you have people that will turn 65 this month?
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

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
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Slight variation -
select id from tab1 where trunc(add_months(bdate,65*12),'mm') = trunc(sysdate,'mm');
>>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.