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 )
RETURNS smalldatetime
AS
BEGIN
declare @.BackDay int
declare @.OADay int
set @.OADay = dbo.GetOADate(@.FromDate)
declare curDaysBack cursor scroll for
select OADate
from Data_Daily
where ItemID = @.ItemID and OADate <= @.OADay
order by OADate desc
open curDaysBack
fetch absolute @.DaysBack
from curDaysBack
into @.BackDay
close curDaysBack
deallocate curDaysBack
if @.BackDay is null
begin
set @.BackDay= ( select Min(OADate) from Data_Daily where ItemID = @.ItemID and OADate <= @.OADay )
end
RETURN convert(smalldatetime, @.BackDay)
END
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?DATEDIFF?
You know your example is only selecting 1 row....|||It can't be DateDiff, because not every day is a trading day, obviously. I need to go back n trading days, meaning entries for the given ticker between two dates. And yes, it is only selecting one row, which is the idea.sql
Showing posts with label fastest. Show all posts
Showing posts with label fastest. Show all posts
Subscribe to:
Posts (Atom)