SQL Server - Find the value for recent most date

Question

I need to update a FINAL table which has ID and Dates using ID & Dates in DATA table. I'm using SQL Server. Imagine foreign currency to USD conversion. A security may trade on Monday...Sunday, but the forex rates may be available from Monday..Friday only. So the Saturday & Sunday closing price would use Friday's exchange rate. Thanks!

CREATE TABLE #Data (Date datetime, ID varchar(50), Value float) 
INSERT INTO #Data (Date, ID, Value) SELECT '12/5/2013', 'ABC', 100 
INSERT INTO #Data (Date, ID, Value) SELECT '12/6/2013', 'XYZ', 50 
INSERT INTO #Data (Date, ID, Value) SELECT '12/8/2013', 'XYZ', 150

CREATE TABLE #Final (Date datetime, ID varchar(50), Value float) 
INSERT INTO #Final (Date, ID) SELECT '12/5/2013', 'ABC' 
INSERT INTO #Final (Date, ID) SELECT '12/7/2013', 'XYZ'  
INSERT INTO #Final (Date, ID) SELECT '12/8/2013', 'XYZ'

SELECT * FROM #Data

UPDATE  #Final 
SET     Value = #Data.Value 
FROM    #Final 
        JOIN #Data ON #Data.ID= #Final.ID AND #Data.Date<= #Final.Date --WRONG

SELECT * FROM #Final

DROP TABLE #Data DROP TABLE #Final

Desired output in #Final

    2013-12-05 00:00:00.000  |  ABC  |  100
    2013-12-07 00:00:00.000  |  XYZ  |  50
    2013-12-08 00:00:00.000  |  XYZ  |  150

Answer 1

Nicarus answer's would update all fields for a matching ID, regardless of the date at which the "transaction" happened. Ie, this means that for "XYZ" you will get in #Final, regardless of the date, the same value "150".

If I understand correctly, if there is a match in both #Data and #Final between the ID, and the date, you want to update the "Value" field in #Final with the "Value" data from #Data. If there is a match on ID, but no match on the date, take the "Value" from the most recent "possible" date in #Data ?

Example : For the '12/5/2013', 'ABC' the value in #Final should be 100 For the '12/7/2013', 'XYZ', the value in #Final should be 50 (because it's the value from the previous day, ie the 12/6) For the '12/8/2013', 'XYZ', the value in #Final should be 150.

Is that the case ?

Edit :

Here is a more proper answer

UPDATE F
  --When we have data directly from D1, it means #Data contains a "Value" for the
  --specific date in #Final, so let's take it.
  --Otherwise, let's find the "Value" for the previous available date in #Data, and put
  --this value instead
  SET F.Value = CASE WHEN D1.Value IS NULL THEN D2.Value ELSE D1.Value END
FROM    #Final F
  LEFT JOIN #Data D1
    ON D1.ID = F.ID
    AND D1.Date = F.Date
  LEFT JOIN (
             SELECT 
               F.Date
               ,F.ID
               ,D.Value
               ,Rownum = ROW_NUMBER() OVER(
                                           PARTITION BY F.ID, F.Date 
                                           ORDER BY D.Date DESC
                                          ) --Here we look for the first date prior to
                                            --the one that we have in #Final
             FROM #Final F 
               LEFT JOIN  #Data D 
                 ON D.ID = F.ID 
                 AND D.Date < F.Date 
             WHERE D.Date IS NOT NULL --Not really necessary, just removing some
                                      --useless lines
           ) D2
    ON D2.ID = F.ID
    AND Rownum = 1 --Taking the first date prior to the one in #Final
    AND D2.Date = F.Date

Answer 2

You could sequence it and update:

UPDATE tgt
SET tgt.Value = src.Value
FROM #FINAL tgt
JOIN
(
SELECT
    ID,
    Value,
    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Date DESC) AS Seq
FROM
    #DATA
) src
ON (tgt.ID = src.ID)
AND (src.Seq = 1)

---

You can see what this would do by running this:

SELECT
    *
FROM #FINAL tgt
JOIN
(
SELECT
    ID,
    Value,
    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Date DESC) AS Seq
FROM
    #DATA
) src
ON (tgt.ID = src.ID)
AND (src.Seq = 1)