Cursor newbie -- fetch next / prior

Question

Cursor newbie -- fetch next / prior

cheetoh

SSC Enthusiast

Points: 146
More actions
April 8, 2004 at 12:28 pm

#163331

Here is the senario...We make stuff I'll call "Foo" in a batch process. We time stamp creation and finish for each batch. We need 'batch cycle times' meaning that you take the Datediff of create time and the previous batch finish time.
For example: Batch 001 finished @ 09:00, Batch 002 started at 11:00, batch cycle is datediff(n, finish, start) or 120 mins.
I have been playing with a temp table and cursor combo and am getting close but cannot figure out the process. The whole fetch next / fetch prior issue has got me stumped. Any help or examples are appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mads Holm Mr or Mrs. 500 Points: 564 More actions · Answer 1

A little more info would be helpful. What does "We need 'batch cycle times'" mean. I understand that you want to do something about the intervals between batch runs. But you know the datediff function, so what is the problem? What sort of results do you expect, what is the exact form of your stored time stamps, etc?

Mads Holm

Michael R Schmidt Mr or Mrs. 500 Points: 532 More actions · Answer 2

I dont think you want to use fetch prev, I think you want to create a temporary variable that holds the previous batch start time. then you can use this value in your datediff and you still get the performance of a fast_forward cursor. Does that make sense?

Michael R. Schmidt
Developer

cheetoh SSC Enthusiast Points: 146 More actions · Answer 3

Mads...

The question buried in my post was 'Does anyone have syntax that they could share that would get me the results that I explained I needed?'

Mikey...

That makes sense in that at least I understand what you are mentioning. I am not that strong in T-SQL yet so is there more guidance that someone could provide? Perhaps the code I have so far would shed some light.

CREATE TABLE #BatchCycle (Batchname varchar(20), Created datetime, XferTime datetime, Duration numeric)

DECLARE @Batchname varchar(20),

@Created datetime,

@XferTime datetime,

@Duration numeric

SET @Duration = 0

DECLARE dr_cursor CURSOR

STATIC

FOR

SELECT BatchName, Created, XferTime

FROM TableA

OPEN dr_cursor

FETCH NEXT FROM dr_cursor INTO @Batchname, @Created, @XferTime

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT #BatchCycle VALUES (@Batchname, @Created, @XferTime, @Duration)

FETCH NEXT FROM dr_cursor INTO @Batchname, @Created, @XferTime

SET @Duration = DateDiff("n", @Created, @XferTime)

END

CLOSE dr_cursor

DEALLOCATE dr_cursor

SELECT * FROM #BatchCycle ORDER BY Created DESC

DROP TABLE #BatchCycle

This gets me the datediff from within the row but I am needing to get the 'Xfertime' from the previous row and as fundamental as that sounds it is stumping me. TIA!

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 4

Numerous ways you can do this either, SQL Mag had a article describing the different techniques.

One is a cursor (pseudo code)

Cursor is select batchname, creationdate, xfretime ORDER BY creation date

fetch from cursor batchname, creationdate, xfertime

into this_ variables

loop

inset into new table (batchname, datediff (this_creationDate, last_creationdate)

store creationdate and xfer time in last_ variables

fetch from cursor batchname, creationdate, xfertime

end of loop

Another is to use qub queries.

select batchname, datediff(last.creationdate, (select max(creationdate) from tablea last where last.creationdate < this.creationdate)

from tablea this

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

cheetoh SSC Enthusiast Points: 146 More actions · Answer 5

Simon,

Thank you for the assistance. Your guidance helped me solve the issue and learn, couldn't ask for more. Thanks again.