ansaurus

Question

Answer 1

+1 A:

UPDATE  Table1
SET     Date2 = t2.Date2
FROM    Table1 t1
JOIN    Table2 t2
    ON  t1.Name = t2.Name
    AND ABS(DATEDIFF(d, t1.Date1, t2.Date2)) = (SELECT  MIN(ABS(DATEDIFF(d, t1.Date1, t2.Date2)))
                                                FROM    Table1 t1
                                                JOIN    Table2 t2
                                                    ON  t1.Name = t2.Name
                                                )

Check if you need ABS - I guess you do.

Also the query does not handle the case where there are 2 dates in Table2 with the same distance to the Date1, but from different sides.

van 2009-10-26 06:41:03

You may also be interested in this page: http://dotnetslackers.com/Community/blogs/bmains/archive/2006/11/30/DateDiff-in-SQL-Server.aspx - in particular, the first comment posted there, in which the poster suggests a method that doesn't use `DATEDIFF` directly in the `WHERE` clause, because that would prevent any indices on the tables from being used properly.

David 2009-10-26 06:50:05

To deal with the symmetric date difference, I think you end up deciding that you always use the earlier (or later) of the two dates, and apply a MIN(t2.date1) to the criteria too. But good thinking to note that you hadn't got that case covered.

Jonathan Leffler 2009-10-26 06:56:10

Answer 2

+1 A:

WITH abcd AS
 (
 SELECT t1.Name,t1.Date1, t2.Date2
  ,RANK() OVER (ORDER BY ABS(DATEDIFF(dd, t1.Date1, t2.Date2)) ASC) AS rnk
 FROM 
  Table1 AS t1 
  JOIN Table2 AS t2 ON t1.Name = t2.Name
 )
UPDATE Table1 SET
    [Date2] = (SELECT TOP(1) [Date2] FROM abcd WHERE rnk = 1)

Damir Sudarevic 2009-11-10 22:48:07

ansaurus

tags:

views:

answers:

SQL update to record with nearest data

related questions