CREATE TABLE #PriceCompare (

ITEMNMBR varchar(31),

ITEMDESC varchar(101),

DOCDATE datetime,

UNITCOST numeric(19, 5),

rownum int identity

)

INSERT INTO #PriceCompare

SELECT

RTRIM(PH.ITEMNMBR) [ITEMNMBR],

RTRIM(i.ITEMDESC) [ITEMDESC],

ph.DOCDATE,

ph.UNITCOST

FROM IV00101 i

INNER JOIN IV30300 ph

ON i.ITEMNMBR = ph.ITEMNMBR

ORDER BY i.ITEMNMBR, ph.DOCDATE

SELECT

currow.ITEMDESC,

ISNULL(prevrow.UNITCOST, 0.00) AS OldCost,

currow.UNITCOST AS RangeCost,

CONVERT(varchar(10), currow.DOCDATE, 110) AS StartDate,

ISNULL(CONVERT(varchar(10), nextrow.DOCDATE, 110), 'CURRENT') AS EndDate

FROM #PriceCompare currow

LEFT JOIN #PriceCompare nextrow

ON currow.rownum = nextrow.rownum - 1

AND currow.ITEMNMBR = nextrow.ITEMNMBR

LEFT JOIN #PriceCompare prevrow

ON currow.rownum = prevrow.rownum + 1

AND currow.ITEMNMBR = prevrow.ITEMNMBR

DROP TABLE #PriceCompare