Performing a FETCH RELATIVE without a cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Performing a FETCH RELATIVE without a cursor

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-14 : 08:53:31

Russell writes "I have the following table, which is linked relationally to a table called Tag:

CREATE TABLE [dbo].[Val] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[TagId] [int] NULL ,
[DogTime] [datetime] NULL ,
[Val] [float] NULL
) ON [PRIMARY]

The above table will, for a specific TagId, hold a series of Val values over a period of one year with a value every 5 seconds. To graph this data to watch the Vals change over time I would need about 500 values. Which is fine if my WHERE clause spans a time period of 42 minutes (with a value every 5 seconds, I’d need a 42 minutes time span to get my 500 values). But if I want to graph the Vals for a period of one month or one year I need 500 values spread over the time period at some interval.

I have the following stored procedure that uses a cursor and the FETCH RELATIVE command. It looks like it works in Query Analyser, but seems to return the results in separate tables, so when I call the stored proc on an ASP page I only get the first row returned to the record set. That problem I’m sure I can get around, but I’m looking for a different technique altogether.

CREATE PROCEDURE dbo.spValWithRes
@resolution smallint,
@sdate datetime,
@fdate datetime,
@tagid int

AS
DECLARE @TempTableName varchar

DECLARE mycur CURSOR STATIC SCROLL READ_ONLY
FOR SELECT TagId, Dogtime, Val
FROM Val
WHERE Dogtime >= @sdate AND dogtime <= @fdate AND TagId = @tagid
ORDER BY Dogtime

OPEN mycur
FETCH NEXT FROM mycur

WHILE @@FETCH_STATUS = 0
BEGIN
FETCH RELATIVE @resolution FROM mycur
END

CLOSE mycur
DEALLOCATE mycur
GO

It has been suggested to me that instead of moving through the records relative to the number of records with FETCH RELATIVE, it would be better to move relative to time (the DogTime field), as there may be periods where there are no values.

This is where I’m stuck. I need help with the SQL to step through records relative to the datetime field (DogTime). This can be done with a cursor, as performance is not critical (the users are patient!), but I’ll still need help to get all the records returned in one go.

So if there is a way to return all records from a cursor as if it were a normal SELECT statement then that would be very helpful. I’ve read about Derived tables, and was wondering if these can be used with a cursor?

Thanks in advance,
Russell"

TonyH
Starting Member

29 Posts

Posted - 2002-01-14 : 11:54:10

Hi,

Could you do it without a cursor, by calculating the interval time for 500 intervals between the start and end dates.

I haven't tested the following sp, but it may have the root of something useful, though I'm not quite sure how 'there may be periods where there are no values' fits into the equation (what happens under those circumstances?). Anyway good luck.

CREATE PROCEDURE dbo.spValWithRes
@sdate datetime,
@fdate datetime,
@tagid int

AS
DECLARE @intervalsecs int,
@intremainder smallint,
@lowerint int,
@upperint int,
@lowerdiff int,
@upperdiff int

-- calc the no of secs in 1 interval by dividing the diff between start and end dates by 500
select @intervalsecs = (datediff(ss, @sdate, @fdate)/500)

-- this may well not be exactly divisable by 5., so
select @intremainder = @intervalsecs % 5
if @intremainder <> 0
begin
-- round down/up to nearest 5 secs
select @lowerint = @intervalsecs - @intremainder,
@upperint = @intervalsecs + (5 - @intremainder)

-- now whether to use the lower or upper int
-- see which when multiplied by 500 is closer to the actual start/end date range
select @lowerdiff = datediff(ss, @sdate, @fdate) - (@lowerint * 500),
@upperdiff = (@upperint * 500) - datediff(ss, @sdate, @fdate)

if @lowerdiff <= @upperdiff
begin
select @intervalsecs = @lowerint
end
else
begin
select @intervalsecs = @upperint
end

end

SELECT TagId,
Dogtime,
Val
FROM Val
WHERE DogTime BETWEEN @sdate AND DATEADD(ss, (500* @intervalsecs), @sdate)
AND (datediff(ss, @sdate, Dogtime) % @intervalsecs) = 0
AND TagId = @tagid
ORDER BY Dogtime

TonyH
www.SQLCoder.com - Free Code generation for SQL Server 7/2000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources