Linking to the Previous Row

/*The client would like to see a report showing the Item name, the old price, the new price and the date range for which the new price was applied. This type of question has often given headaches to sql developers, as it usually involved messy subqueries using the max / min functions. Some DBAs prefer to store both the start date and end date in the table, but this too causes problems, as it is difficult to keep subsequent records in synch, especially when a modification occurs.*/

//Input:

//Output:

declare @Items table
(ID int, Name VARCHAR(1000))

//Sample Data
insert into @Items(ID,Name) values(1,'Vaccum Cleaner');
insert into @Items(ID,Name)values(2,'Washing Machine');
insert into @Items(ID,Name)values(3,'Tooth Brush');

//Price History Table Structure
declare @PriceHistory table(
ItemId int,
PriceStartDate datetime,
Price money
)

//Price History Table Data
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(1,'03/01/2004',250.00)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(1,'06/15/2005',219.99)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(1,'01/03/2007',189.00)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(1,'02/03/2007',200.00)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(2,'07/12/2006',650.00)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(2,'01/03/2007',550.00)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(3,'01/01/2005',1.99)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(3,'01/01/2006',1.79)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(3,'01/01/2007',1.59)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(3,'01/01/2008',1.49);

//Query to deliver the desired output.
WITH PriceCompare AS (
SELECT i.ID, ph.ItemId, ph.PriceStartDate, ph.Price,
ROW_NUMBER() OVER (Partition BY ph.ItemId ORDER BY PriceStartDate) AS rownum
FROM @Items i INNER JOIN @PriceHistory ph
ON i.ID = ph.ItemId)


SELECT currow.ItemId, prevrow.Price AS PreviousPrice, currow.Price AS CurrentPrice, currow.PriceStartDate AS StartDate, nextrow.PriceStartDate AS EndDate
FROM PriceCompare currow
LEFT JOIN PriceCompare nextrow
ON currow.rownum = nextrow.rownum - 1
AND currow.ItemId = nextrow.ItemId
LEFT JOIN PriceCompare prevrow
ON currow.rownum = prevrow.rownum + 1
AND currow.ItemId = prevrow.ItemId