How can you use SQL to return values for a specified date or closest date < specified date?

I've written an SQL statement to return a list of prices based on a date parameter, but I am finding that on some dates, the price is missing. I am looking for a way to modify this statement to return the price on the date specified, but if that is not available return the price for the most recent price available before the date specified.

Select date, grp, id, price
From
  price_table
Where
  date In ('12/31/2009', '11/30/2009') And
  grp In ('Group1')

For example, in the I would like to be able to re-write th开发者_开发知识库e statement above to return all of the records below, showing appropriate parameter dates for all records. Assume this is a subset of a table with daily prices and the values below are the last prices for the months noted.

12/31/2009 Group1 1111 100
12/31/2009 Group1 2222 99
12/29/2009 Group1 3333 98
11/30/2009 Group1 1111 100
11/28/2009 Group1 2222 99
11/30/2009 Group1 3333 98

UPDATE: Thanks to some help from srgerg below, I have been able to create a statement that works for one date at a time, but I would still like to find a way to pass multiple dates to the query.

Select p1.date, p1.grp, p1.id, p1.price
From
  price_table As p1 Inner Join
  (Select Max(p2.date) As maxdt, id
    From
      price_table As p2
    Where
      p2.date <= '12/31/2009'
    Group By
      p2.id) As p On p.maxdt = p1.date And p1.id = p.id
Where grp in ('Group1')

You could try something like this:

SELECT date, grp, id
    , (SELECT TOP 1 price
       FROM price_table P2
       WHERE P1.id = P2.id
       AND P1.grp = P2.grp
       AND P2.date <= P1.date
       ORDER BY P2.date DESC)
FROM price_table P1
WHERE P1.date IN ('12/31/2009', '11/30/2009')
AND P1.grp IN ('Group1')

Edit To get the last record for each month, group and id you could try this:

SELECT date, grp, id, price
FROM price_table P1
WHERE P1.date = (SELECT MAX(date)
                 FROM price_table P2
                 WHERE P1.grp = P2.grp
                 AND P1.id = P2.id
                 AND YEAR(P1.date) = YEAR(P2.date)
                 AND MONTH(P1.date) = MONTH(P2.date))
AND P1.grp In ('Group1')

Here's my approach to solving to this problem:

1. Associate every search date with a date in the table.
2. Use search dates as (additional) group terms.
3. Rank the dates in descending order, partitioning them by group terms.
4. Select those with the desired group term values and rank = 1.

The script:

WITH price_grouped AS (
  SELECT
    date, grp, id, price,
    dategrp = CASE
      WHEN date <= '11/30/2009' THEN '11/30/2009'
      WHEN date <= '12/31/2009' THEN '12/31/2009'
      /* extend the list of dates here */
    END
  FROM price_table
),
price_ranked AS (
  SELECT
    date, grp, id, price, dategrp,
    rank = RANK() OVER (PARTITION BY grp, dategrp ORDER BY date DESC)
  FROM price_grouped
)
SELECT date, grp, id, price
FROM price_ranked
WHERE grp IN ('Group1')
  AND rank = 1

The above solution may seem not very handy because of the necessity to repeat each search date twice. An alternative to that might be to define the search date list as a separate CTE and, accordingly, assign the dates in a different way:

WITH search_dates (Date) AS (
  SELECT '11/30/2009' UNION ALL
  SELECT '12/31/2009'
  /* extend the list of dates here */
),
price_grouped AS (
  SELECT
    p.date, p.grp, p.id, p.price,
    dategrp = MIN(d.Date)
  FROM price_table p
    INNER JOIN search_dates d ON p.date <= d.Date
  GROUP BY
    p.date, p.grp, p.id, p.price
),
price_ranked AS (
  SELECT
    date, grp, id, price, dategrp,
    rank = RANK() OVER (PARTITION BY grp, dategrp ORDER BY date DESC)
  FROM price_grouped
)
SELECT date, grp, id, price
FROM price_ranked
WHERE grp IN ('Group1')
  AND rank = 1

But take into account that the former solution will most probably be more performant.