Newbie - Query with loop

SQL Apprentice Question
I am relatively new to Transact SQL and need some help on what should be a
simple query...


I have a table with
* Item
* Cost
* MonthEndingDate (e.g. 1/31/2006, 2/28/2006)


I need a query to return the Cost for a given month, but if the cost is null
or there is no record, I need the previous month's Cost. If the previous
month's Cost is null or there is no record, I need the month before that
(going back 3 months). If all three months are null then I need a null.


Example


Item Cost MonthEnding
Book 10.25 1/31/2006
Book 10.50 2/28/2006
Pen 1.07 2/28/2006
Pen 1.08 3/31/2006
Pen 1.10 4/30/2006
Pencil .10 12/31/2006
Pencil .15 1/31/2006


I would need the query to return:


Item Cost
Book 10.50
Pen 1.10
Pencil null


Any help would be greatly appreciated!! Thanks in advance!


Celko Answers
It really helsp if you post DDL; her is my guess about keys and
constaints... and the table name!

CREATE TABLE ItemSummary
(month_end DATETIME NOT NULL,
item_name VARCHAR(20) NOT NULL,
item_cost DECIMAL(8,2) NOT NULL,
PRIMARY KEY (month_end, item_name));


INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Book', 10.25, '2006-01-31');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Book', 10.50, '2006-02-28');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Pen', 1.07, '2006-02-28');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Pen', 1.08, '2006-03-31');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Pen', 1.10, '2006-04-30');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Pencil', 0.10, '2006-12-31');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Pencil', 0.15, '2006-01-31');


This answer is more of a demonstration of soem coding tricks with
COALESCE(), but it might run better than you think, if the optimizer
does early evaluation on the COALESCE() list.


SELECT DISTINCT item_name,
COALESCE (
(SELECT MAX (item_cost)
FROM ItemSummary AS I2
WHERE month_end = '2006-04-30'
AND I2.item_name = I1.item_name),
(SELECT MAX(item_cost)
FROM ItemSummary AS I2
WHERE month_end = '2006-03-31'
AND I2.item_name = I1.item_name),
(SELECT MAX(item_cost)
FROM ItemSummary AS I2
WHERE month_end = '2006-02-28'
AND I2.item_name = I1.item_name)
) AS recent_cost


FROM ItemSummary AS I1;


item_name recent_cost
==================
Book 10.50
Pen 1.10
Pencil NULL


I hard-wired the month ends, but you can use a DATEDIFF function and a
parameter to make this more general. A better way would be with a
report period calendar table.