The other day I asked Ben to help me with a thorny problem, I needed to match records in one table with records in another table using a date range. Here’s the table I needed to update, called tblWidgets:

WidgetID DateProduced   WidgetWeekID
-------  ------------  ----
1              1/2/11
2              1/16/11
3              1/31/11

WidgetWeekID comes from another table called tblWidgetWeeks:

WidgetWeekID     StartDate    EndDate
 ------------     --------   --------
 1                12/29/11    1/5/11
 2                1/6/11        1/12/11
 3                1/13/11      1/20/11
 4                1/21/11      1/28/11
 5                1/29/11      2/5/11

Click for a free quote.

I needed a query that would find DateProduced between StartDate and EndDate and update WidgetWeekID with the right number. Here’s the solution he came up with:

UPDATE tblWidgets AS w
INNER JOIN tblWidgetWeeks AS wwk
  ON w.[Date] >= wwk.StartDate AND w.[Date] <= wwk.EndDate
 SET w.WidgetWeekID = [wwk].[WidgetWeekID];

What's brilliant about Ben's solution is the join on the date range, a far better solution than using code. Once I ran the query the destination table looks like this:

WidgetID DateProduced   WidgetWeekID
-------  ------------  ----
1              1/2/11    1
2              1/16/11   3
3              1/31/11   5

What other unusual joins have you done? Please comment below. Thanks Ben!