How do I join a sparse table and fill rows between in SQL Server

How can I apply weights from a one table to another [Port] where the weight table has sparse dates?

[Port] table

    utcDT                   UsdPnl
   -----------------------------------------------
    2012-03-09 00:00:00.000 -0.00581815226439161
    2012-03-11 00:00:00.000 -0.000535272460588547
    2012-03-12 00:00:00.000 -0.00353079778650661
    2012-03-13 00:00:00.000 0.00232882689252497
    2012-03-14 00:00:00.000 -0.0102592811199384
    2012-03-15 00:00:00.000 0.00254451559598693
    2012-03-16 00:00:00.000 0.0146718613139845
    2012-03-18 00:00:00.000 0.000425144543842752
    2012-03-19 00:00:00.000 -0.00388548271428044
    2012-03-20 00:00:00.000 -0.00662423680184768
    2012-03-21 00:00:00.000 0.00405506208635343
    2012-03-22 00:00:00.000 -0.000814822806982203
    2012-03-23 00:00:00.000 -0.00289523953346103
    2012-03-25 00:00:00.000 0.00204150859774465
    2012-03-26 00:00:00.000 -0.00641635182718787
    2012-03-27 00:00:00.000 -0.00107168420738448
    2012-03-28 00:00:00.000 0.00131000520696153
    2012-03-29 00:00:00.000 0.0008223678402638
    2012-03-30 00:00:00.000 -0.00255345945390133
    2012-04-01 00:00:00.000 -0.00337792814650089

[Weights] table

utcDT                     Weight
--------------------------------
2012-03-09 00:00:00.000     1
2012-03-20 00:00:00.000     3
2012-03-29 00:00:00.000     7

So, I want to use the weights as if I had a full table like this below. i.e. change to new weight on first day it appears in [Weights] table:

    utcDT                   UsedWeight   
    ----------------------------------
    2012-03-09 00:00:00.000     1
    2012-03-11 00:00:00.000     1
    2012-03-12 00:00:00.000     1
    2012-03-13 00:00:00.000     1
    2012-03-14 00:00:00.000     1
    2012-03-15 00:00:00.000     1
    2012-03-16 00:00:00.000     1
    2012-03-18 00:00:00.000     1
    2012-03-19 00:00:00.000     1
    2012-03-20 00:00:00.000     3
    2012-03-21 00:00:00.000     3
    2012-03-22 00:00:00.000     3
    2012-03-23 00:00:00.000     3
    2012-03-25 00:00:00.000     3
    2012-03-26 00:00:00.000     3
    2012-03-27 00:00:00.000     3
    2012-03-28 00:00:00.000     3
    2012-03-29 00:00:00.000     7
    2012-03-30 00:00:00.000     7
    2012-04-01 00:00:00.000     7

Answer

You can use apply:

select p.*, w.*
from port p outer apply
     (select top (1) w.*
      from weights w
      where w.utcDT <= p.utcDT
      order by w.utcDT desc
     ) w;

outer apply is usually pretty efficient, if you have the right indexes. In this case, the right inex is on weights(utcDT desc).