Unanswered: Row offset values
What is the fastest way to select a value offset by n rows from the start row? I used to use a cursor with FETCH ABSOLUTE in Sybase SQLAnywhere, but this is incredibly slow in SQL Server. Here's the current function I'm using:
FUNCTION dbo.TradingDaysBack ( @ItemID int, @FromDate smalldatetime, @DaysBack int )
declare @BackDay int
declare @OADay int
set @OADay = dbo.GetOADate(@FromDate)
declare curDaysBack cursor scroll for
where ItemID = @ItemID and OADate <= @OADay
order by OADate desc
fetch absolute @DaysBack
if @BackDay is null
set @BackDay= ( select Min(OADate) from Data_Daily where ItemID = @ItemID and OADate <= @OADay )
RETURN convert(smalldatetime, @BackDay)
The idea is to get the date n rows of data back from the starting date (i.e. 30 trading days back from 12/1/2003). Any ideas?
"Border relations between Canada and Mexico have never been better."
-George W. Bush