neeed to eleminate overlaps in dates from query

From: Srini <gi.srinivas_at_gmail.com>
Date: 13 Oct 2005 20:38:56 -0700
Message-ID: <1129261136.622259.180820@f14g2000cwb.googlegroups.com>

Hi,
I need help on the overlap of dates between the records.

Please find the query below(For Grade Details).

select ,HR_GENERAL.DECODE_GRADE(paaf.GRADE_ID) Grade
,paaf.effective_start_date
,paaf.effective_end_date

from per_all_People_f papf
,per_all_assignments_f paaf
,PER_ASSIGNMENT_STATUS_TYPES_V past

where papf.person_id = paaf.person_id

and past.ASSIGNMENT_STATUS_TYPE_ID = paaf.ASSIGNMENT_STATUS_TYPE_ID
and paaf.BUSINESS_GROUP_ID = 111
and papf.person_type_id = 1195
and papf.employee_number = '40003'

and (paaf.CHANGE_REASON = 'PR_PROM' OR paaf.CHANGE_REASON IS NULL) order by paaf.effective_start_date;

Here in the result file, there are 2 dates. effective_start_date and Effective_end_Dates.

GRADE EFFECTIVE_START_DATE EFFECTIVE_END_DATE

5 1/1/2000 2/11/2000
6 2/12/2000 2/13/2000
8 2/14/2000 2/15/2000
9 2/18/2000 10/12/2005

Here whenever the overlap comes from start date to end date, it should pick up the one day less to the start date.

here for Grade 8 to 9 there is overlap like grade 8's effective end date is 15-Feb-2005, and grade 9's effective start date is 18-FEB-2000. SO here what i need is , for grade 8 the effective end date should be grade 9's effective_start_date-1 that is 17-Feb-2000.

And the last row's effective_end_date is always 31-DEC-4712.

Please help me on this.

Thanks and regards,
Srinivas. Received on Thu Oct 13 2005 - 22:38:56 CDT