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 PGRPDESCfrom D550M eleft join D500M per on e.PERSON_REF = per.PERSON_REFleft join D100M CGrp on e.LEVEL1_PAY_STR_REF = CGrp.REFleft join D100M PGrp on e.PAY_STR_REF = PGrp.REFjoin D500M per on e.PERSON_REF = per.PERSON_REF)select EMPNO,FORENAME,SURNAME,BIRTH_DATEfrom CTE_EmployeeInfo where--BIRTH_DATE >= add_months(sysdate, -16*12) BIRTH_DATE >= add_months(sysdate, -65*12) -7AND sex = 'M'
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.