Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> neeed to eleminate overlaps in dates from query
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'
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
![]() |
![]() |