Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    18

    Unanswered: Catch Date Gaps in Different Records

    I need to write a procedure to check if I have any gaps in my dates...

    Start Date............End Date
    10/6/2004............10/6/2005
    10/6/2003............10/6/2004
    7/10/2003..........10/6/2003
    7/10/2002.............7/10/2003

    What I need to do is test the End Date to the next row under the Start Date Column. (in bold to clarify) (moving upward from bottom)
    Code:
        sSQL011$ = "SELECT * FROM TableName " & _
                 "WHERE ID = '" & ID & "'"
        Set rs011 = DB.OpenRecordset(sSQL011$, dbOpenDynaset)
        Set Data1.Recordset = rs011
        rs011.FindFirst "ID = " & ID
    
    With rs011
       do while .eof = False
            
            if .eof then
                exit sub
            end if
    
             if rs011("StartDate")  'from one record, if it is not equal to the Start
             Date in the next record then  
             MsgBox "Gap in Dates for: " + ID
    
       loop
    
      end with
    pseudo...
    if the end date in one consecutive row is not the same as the start date in the next row then there is a date gap

    thanks...
    Last edited by McF; 01-30-05 at 17:47.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    moving to sql server forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What database engine are you using (MS-SQL, Jet, Oracle, other)?

    Are multiple active rows possible? For example, is it possible to have:
    Code:
    Start           End
    1900-01-01  1925-01-01
    1910-02-25  1930-02-16
    1925-01-01  1950-01-01
    1930-06-15  1980-07-04
    1960-03-17  2000-01-01
    If you could have that, would there be gaps or no gaps in the coverage?

    -PatP

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Records in SQL Server have no assumed order unless you specify it.

    There is no such thing as "consecutive row" in SQL Server.

    There is no such thing as "next row" in SQL Server.

    How are you sorting your records? Please do not say: "I am sorting them by the date and time they were entered, though this is not stored anywhere in the table."
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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