Loop to check all 14 days in the pay period
- by Rachel Ann Arndt
Name: Calc_Anniversary
Input: Pay_Date, Hire_Date, Termination_Date
Output: "Y" if is the anniversary of the employee's Hire_Date, "N" if it is not, and "T" if he has been terminated before his anniversary.
Description: Create local variables to hold the month and day of the employee's Date_of_Hire, Termination_Date, and of the processing date using the TO_CHAR function. First check to see if he was terminated before his anniversary. The anniversary could be on any day during the pay period, so there will be a loop to check all 14 days in the pay period to see if one was his anniversary.
CREATE OR replace FUNCTION Calc_anniversary(
incoming_anniversary_date IN VARCHAR2)
RETURN BOOLEAN
IS
hiredate VARCHAR2(20);
terminationdate VARCHAR(20);
employeeid VARCHAR2(38);
paydate NUMBER := 0;
BEGIN
SELECT Count(arndt_raw_time_sheet_data.pay_date)
INTO paydate
FROM arndt_raw_time_sheet_data
WHERE paydate = incoming_anniversary_date;
WHILE paydate <= 14 LOOP
SELECT To_char(employee_id, '999'),
To_char(hire_date, 'DD-MON'),
To_char(termination_date, 'DD-MON')
INTO employeeid, hiredate, terminationdate
FROM employees,
time_sheet
WHERE employees.employee_id = time_sheet.employee_id
AND paydate = pay_date;
IF terminationdate > hiredate THEN
RETURN 'T';
ELSE
IF To_char(SYSDATE, 'DD-MON') = To_char(hiredate, 'DD-MON')THEN
RETURN 'Y';
ELSE
RETURN 'N';
END IF;
END IF;
paydate := paydate + 1;
END LOOP;
END;
Tables I am using
CREATE TABLE Employees ( EMPLOYEE_ID INTEGER,
FIRST_NAME VARCHAR2(15),
LAST_NAME VARCHAR2(25),
ADDRESS_LINE_ONE VARCHAR2(35),
ADDRESS_LINE_TWO VARCHAR2(35),
CITY VARCHAR2(28),
STATE CHAR(2),
ZIP_CODE CHAR(10),
COUNTY VARCHAR2(10),
EMAIL VARCHAR2(16),
PHONE_NUMBER VARCHAR2(12),
SOCIAL_SECURITY_NUMBER VARCHAR2(11),
HIRE_DATE DATE,
TERMINATION_DATE DATE,
DATE_OF_BIRTH DATE,
SPOUSE_ID INTEGER,
MARITAL_STATUS CHAR(1),
ALLOWANCES INTEGER,
PERSONAL_TIME_OFF FLOAT,
CONSTRAINT pk_employee_id PRIMARY KEY (EMPLOYEE_ID),
CONSTRAINT fk_spouse_id FOREIGN KEY (SPOUSE_ID) REFERENCES EMPLOYEES (EMPLOYEE_ID))
/
CREATE TABLE Arndt_Raw_Time_Sheet_data ( EMPLOYEE_ID INTEGER,
PAY_DATE DATE,
HOURS_WORKED FLOAT,
SALES_AMOUNT FLOAT,
CONSTRAINT pk_employee_id_pay_date_time PRIMARY KEY (EMPLOYEE_ID, PAY_DATE),
CONSTRAINT fk_employee_id_time FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES (EMployee_ID));
error FUNCTION Calc_Anniversary compiled
Warning: execution completed with warning
