Historical Prices With Data Gaps

Hi,

I have a SQL2005 db for tracking the prices of products at multiple retailers. The basic structure is, 'products' table lists individual products, 'retailer_products' table lists current prices of the products at multiple retailers, and 'price_history' table records when the price of a product changes at any retailer. The prices are checked from each retailer daily, but a row is added to the 'price_history' only when the price at the retailer changes.

Database create script:
http://www.boltfile.com/directdownload/db_create_script.sql

Full database backup:
http://www.boltfile.com/directdownload/database.bak

Database diagram:
http://www.boltfile.com/directdownload/diagram_0.pdf

I have the following query to retrieve the price history of a given product at multiple retailers:

SELECT
price_history.datetimeofchange, retailer.name, price_history.price
FROM
product, retailer, retailer_product, price_history
WHERE
product.id = 'b486ed47-4de4-417d-b77b-89819bc728cd'
AND
retailer_product.retailerid = retailer.id
AND
retailer_product.associatedproductid = product.id
AND
price_history.retailer_productid = retailer_product.id

This gives the following results:

2008-03-08 Example Retailer 22.3
2008-03-28 Example Retailer 11.8
2008-03-30 Example Retailer 22.1
2008-04-01 Example Retailer 11.43
2008-04-03 Example Retailer 11.4

The question(s) I have are how can I:

1 - Get the price of a product at a given retailer at a given date/time
For example, get the price of the product at Retailer 2 on 03/28/2008. Table only contains data for Retailer 1 for this date, the behaviour I want is when there is no data available for the query to find the last data at which there was data from that retailer, and use the price from that point - i.e. so for this example the query should result in 2.3 as the price, given that was the last recorded price change from that retailer (03/08/2008).

2 - Get the average price of a product at a given retailer at a given date/time
In this case we would need to perform (1) across all retailers, then average the results

I'd really appretiate anyone's help on this :)

many thanks in advance,

dg