Hello everyone, I was wondering if I could get some help with this query. I'm trying to find out unpaid leave days for an employee and this query does the job. The problem is this. The query returns date_start and date_end and absence_days (working days). What I really want to do is to check if there employee has any unpaid leave between any 2 dates of my own choosing.
So lets say date_start is 28-JAN-2011 and date_end is 03-FEB-2010, I might choose to find out unpaid working days between 01-FEB-2011 and 15-FEB-2011. If I choose a date completely outside date_start and date_end, then return 0. Can anyone help guide me on the best approach?

SELECT absence_days, date_start, date_end
FROM per_absence_attendances_v paav, per_all_assignments_f paaf
WHERE paav.person_id = paaf.person_id
AND paaf.assignment_id = :p_assignment_id
AND paav.category_meaning = 'Unpaid Leave'
AND SYSDATE BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;