Check number of consecutive working days of Absence from a list of dates (F
elenadJan 27 2010 — edited Feb 8 2010Dear all,
We have Oracle Human Resources 11.5.7 Application and I have a request to create an Absence report (using sql*plus coding that can be uploaded on Discoverer for running the report by the user) as follows:
Sample Columns:
ABSENCE_CATEGORY
ABSENCE_TYPE
ABSENCE_START_DATE
ABSENCE_END_DATE
ABSENCE_DAYS
Sample Data:
EMPLOYEE A (FOR ABSENCE PERIOD 2009)
ABSENCE_CATEGORY ABSENCE_TYPE ABSENCE_START_DATE ABSENCE_END_DATE ABSENCE_DAYS
A Annual Leave 27-APR-2009 30-APR-2009 4
A Educational Leave 04-MAY-2009 04-MAY-2009 1
P Annual Leave 05-MAY-2009 12-MAY-2009 6
...
TOTAL: 11 CONTINUOUS WORKING DAYS ON LEAVE
NOTES:
01-MAY-2009 HOLIDAY
02-MAY-2009 & 03-MAY-2009 WEEKEND
09-MAY-2009 & 10-MAY-2009 WEEKEND
EMPLOYEE B (FOR ABSENCE PERIOD 2009)
ABSENCE_CATEGORY ABSENCE_TYPE ABSENCE_START_DATE ABSENCE_END_DATE ABSENCE_DAYS
A Annual Leave 18-MAY-2009 29-MAY-2009 10
...
TOTAL: 10 CONTINUOUS WORKING DAYS ON LEAVE
EMPLOYEE C (FOR ABSENCE PERIOD 2009)
ABSENCE_CATEGORY ABSENCE_TYPE ABSENCE_START_DATE ABSENCE_END_DATE ABSENCE_DAYS
A Annual Leave 08-JUN-2009 17-JUN-2009 8
...
TOTAL: 8 CONTINUOUS WORKING DAYS ON LEAVE
IF AN EMPLOYEE TOOK 10 OR MORE CONSECUTIVE WORKING DAYS SHOULD BE EXCLUDED FROM THE REPORT.
THEREFORE FROM THE ABOVE EXAMPLE ONLY EMPLOYEE C SHOULD BE RETURNED FROM THE QUERY.
Any ideas/feedback if and how can achieve the above output will be much appreciated.
Thanking you in advance,
Best regards,
Elena