-
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
-
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
-
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);
