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?
If it doesnt, could someone show me where im going wrong?
Many thanks,
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'
If it doesnt, could someone show me where im going wrong?
Many thanks,
Oracle Database
Last Comment
slightwv (䄆 Netminder)
Sounds like you are only looing for people that turned 65 the month the report runs.
Try this:
(bdate>=add_months(trunc(s
Try this:
(bdate>=add_months(trunc(s
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.
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 -
@Ors-Ankh-Aten - didnt think of that. Thanks for the heads up.
Rich
@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))
@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?
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.
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);
ASKER
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
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Slight variation -
select id from tab1 where trunc(add_months(bdate,65*
select id from tab1 where trunc(add_months(bdate,65*
>>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.
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.
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.