Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    Boston, MA
    Posts
    11

    Question 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 )
    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?
    Mike

    "Border relations between Canada and Mexico have never been better."
    -George W. Bush

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    DATEDIFF?

    You know your example is only selecting 1 row.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2004
    Location
    Boston, MA
    Posts
    11
    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.
    Mike

    "Border relations between Canada and Mexico have never been better."
    -George W. Bush

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •