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.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...|||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|||Thanks Guys
I am going to try the SQL script to see if it works with my tables.|||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|||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