Calc_Anniversary Function with a Loop
- 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;
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;
I need help with the loop..
