overlapping dates
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: overlapping dates

  1. #1
    Join Date
    Nov 2001
    Posts
    12
    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

  2. #2
    Join Date
    Jun 2000
    Location
    French Polynesia
    Posts
    16
    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

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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);
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width