overlapping dates

[orareddy]

I need to select the records which r not in a Sequential dates.

Like

ID Start_date End_date Sq.no

1 01-01-2001 03-15-2001 1
1 03-15-2001 05-31-2001 2
1 05-31-2001 06-30-2001 3

2 01-01-2001 02-20-2001 4
2 02-10-2001 03-25-2001 5
2 03-25-2001 04-20-2001 6



I am trying to select the records, the dates are not in ORDER like ID 2
The End_date can’t be Greater then Start_date

Like ID 2 02-10-2001 (Sq.no 5)
it’s greater then the 02-20-2001 Sq.no 4

it should be equal to or after the 02-20-2001.

Every Start_date should be start after the End_date for every ID.

Pl. help for finding the records which r not in a Sequence(date) ORDER.


thanks in advance

[rbouissou]

Use a database trigger or some form trigger to program the constraint in you application, either on a insert or update action. That way the sequence is respected.
It seems to me there is no other way to do it.
Bye

[jmodic]

SELECT * FROM dates d1 WHERE EXISTS
(SELECT null FROM DATES d2
WHERE d2.start_date > d1.end_date
AND d2.id = d1.id
AND d2.seq_no = d1.seq_no - 1);