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 the 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:
Edit To get the last record for each month, group and id you could try this:
Here's my approach to solving to this problem:
rank = 1
.The script:
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:
But take into account that the former solution will most probably be more performant.