Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    9

    Unanswered: Challenging sql code

    I have two tabels : TABLE1 and TABLE2
    TABLE 1 has the following Columns
    OID
    DATE1
    ComonField1
    Field1

    TABLE 2 has the following Columns
    OID
    DATE2
    ComonField1
    Field2

    What I am willing to achieve is the following:
    Is to find amongst the rows in TABLE2 (they are many having the same comonField value), which one has the closest date (DATE2) to a row in TABLE1 (DATE1) DATE@2 must be lower than DATE1 but the closest. I am going to be creating 2 columns in TABLE1 where I will put the values of the row that respond to the previous condition (DATE2 and Field2) in TABLE1.

    I need to go throug all the rows in TABLE1 and do the same thing i.e. find the appropriate row in TABLE2 and take 2 columns values and put them in front of the row in TABLE1.

    Any help please.

  2. #2
    Join Date
    Oct 2003
    Posts
    706

    Re: Challenging sql code

    Originally posted by Nour
    I have two tabels : TABLE1 and TABLE2
    TABLE 1 has the following Columns
    OID
    DATE1
    ComonField1
    Field1

    TABLE 2 has the following Columns
    OID
    DATE2
    ComonField1
    Field2

    What I am willing to achieve is the following:
    Is to find amongst the rows in TABLE2 (they are many having the same comonField value), which one has the closest date (DATE2) to a row in TABLE1 (DATE1) DATE@2 must be lower than DATE1 but the closest. I am going to be creating 2 columns in TABLE1 where I will put the values of the row that respond to the previous condition (DATE2 and Field2) in TABLE1.

    I need to go throug all the rows in TABLE1 and do the same thing i.e. find the appropriate row in TABLE2 and take 2 columns values and put them in front of the row in TABLE1.
    First of all, there is no way to do that with a single SQL query. And you probably don't want to join them because "there are a lot of field values in common."

    What you probably should do is to open a query on each table with the ORDER BY clause so the rows will be sorted the same: by CommonField and by Date. Now you write code that will scan through both tables at the same time... COBOL-style.

    The ORDER BY CommonField will cause all of the field values to be together, and since the two queries are sorted the same way you can decide what to do if the fields match, if one's smaller, if the other's smaller, if you're at end-of-table on one or the other or both... about six different cases. For a match, either the dates match or one's smaller or the other's smaller...

    This problem can be solved with exactly one sequential pass which goes through both of these two streams at the same time. When all those reels of magnetic-tape were spinning in those science fiction movies, this is exactly what they were doing. In fact, when IBM sold punched card tabulators before computers existed, that's what they were doing, too!

    Identically sorted streams of data...
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    sundial, what the heck is your SQL Sever background, anyway? Over the last few days you have given some of the worst advice I've seen on this forum.

    Nour,

    Here is a general solution assuming that the OID fields can't be used to join your two tables. The solution is simpler if it can. This checked out syntactically in Query Analyzer, but without your exact table structure I of course could not test it. Other factors that could come into consideration would be things like duplicate DATE2 values in your second table.

    Look this over and let me know if you have any more question.

    select DateCompare.*,
    table2.*
    from
    (select table1.OID,
    table1.DATE1,
    table1.ComonField1,
    table1.Field1,
    min(datediff(s, table2.DATE2, table1.DATE1)) MinSeconds
    from table1
    inner join table2 on table1.DATE1 >= table2.DATE2
    group by table1.OID,
    table1.DATE1,
    table1.ComonField1,
    table1.Field1) DateCompare
    inner join table2 on datediff(s, table2.DATE2, DateCompare.DATE1) = DateCompare.MinSeconds

    blindman

  4. #4
    Join Date
    Oct 2003
    Posts
    9
    Thanks Guys
    I am going to try the SQL script to see if it works with my tables.

  5. #5
    Join Date
    Oct 2003
    Posts
    9
    Just a question to blindman
    What's the table DateCompare, I am not creating a new table, I willl be updating the TABLE1 by creating two new columns where I will update with the values in the row where the date difference is the minimum.
    A clarification:
    I am comparing only rows from TABLE1 and TABLE2 which have the same OID et comonField
    Thanks
    Last edited by Nour; 10-21-03 at 15:11.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    DateCompare is the name of the subquery, which has to be assigned a name so it can be joined in the outer query. You can give it any name you want as long as you change all the references to it.

    blindman

Posting Permissions

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