Cursor Issues

[Compkitty]

I have a table that looks like this:

RANGE Directories
5136451 ABC
51364522 ABC
5136453 XYZ
5136454 ABC
5136455 XYZ
5136456 CDE
5136457 ABC
5136458 ABC
5136459 XYZ
51364511 CDE
51364512 ABC
51364513 ABC
51364514 ABC
51364515 ABC
51364516 ABC

The Result should be:

DIRECTORY BeginRange EndRange
ABC 5136451 5136451
ABC 51364522 51364522 (they aren't sequential so then get ind. lines
XYZ 5136453 5136453
ABC 5136454 5136454
XYZ 5136455 5136455
CDE 5136456 5136456
ABC 5136457 5136458 There are two ABC's in a row and so the begin is the first, and the end is the last instance before the next Directory. and they are sequential
XYZ
CDE
ABC 51364512 51364516 (this is how the last set of ABCs should look because they are the same directory and in sequential order, but they are coming out like this:

ABC 51364512 51364513
ABC 51364514 51364515
ABC 51364516 51364516

HERE IS MY CODE:

DROP TABLE #testcomp
CREATE TABLE #Testcomp(Directory varchar(30), BeginRange nvarchar(20), EndRange nvarchar(20))

DECLARE @Begin as nvarchar (20)
DECLARE @End as nvarchar(20)
DECLARE @comp as nvarchar(20)
DECLARE @Cdir as varchar (30)
DECLARE @Range as nvarchar (20)
DECLARE @Directory as varchar(30)

DECLARE Compression_Cursor
CURSOR FOR SELECT Range, Directories FROM Tbl_Test_Rtg

OPEN Compression_Cursor
FETCH NEXT FROM Compression_Cursor INTO @Range, @Directory
WHILE @@FETCH_STATUS = 0
BEGIN

SET @Begin = @range
SET @CDir = @Directory
SET @End = @range
FETCH NEXT FROM Compression_Cursor INTO @Range, @Directory
SET @comp = @range

IF @Directory = @Cdir
BEGIN
  IF @comp = @End + 1
   BEGIN
	 SET @End = @comp
	 FETCH NEXT FROM Compression_Cursor INTO @Range, @Directory
    END

   ELSE 
   SET @End = @Begin
   END
ELSE
SET @End = @Begin

INSERT INTO #Testcomp(Directory,BeginRange,EndRange)
VALUES(@Cdir, @Begin, @End)


END 
CLOSE Compression_Cursor
DEALLOCATE Compression_Cursor

SELECT * FROM #testcomp
SELECT * FROM Tbl_Test_Rtg

Can anyone tell me what I'm missing... THANKS

 

[JohnDTampaBay]

SQL Server does not order the results of a SELECT unless ORDER BY is included. There is no guarantee that the rows will be selected in the order they were inserted, even with a clustered index. Is there another column in the table - maybe an identity column - that you could sort on to retrieve the rows in the order you want?

Set-based solutions usually perform better than cursors.
If you provide a column to order the data correctly, perhaps a set-based solution can be written.


--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[Compkitty]

What I want it to do is if the DATA Looks like
ABC 5136454
XYZ 5136456
CDE 5136457
ABC 51364512
ABC 51364513
ABC 51364514
ABC 51364515
ABC 51364516

the Result should look like this
ABC 5136454 5136454
XYZ 5136456 5136456
CDE 5136457 5136457
ABC 51364512 51364516

Rolling up all the Directories that match (ie. ABC)And putting them together until it hits the next non-matching directory.
Ie. it looks at ABC as teh first, puts the range in the begin, then gets the next directory and range, if they match it takes the current range and puts into end, then fetches the next, if doesn't match it puts the current begin in the begin and end (assuming there is only 1 instances) then goes to the next directory...
I might not be making this as clear as I would like. I am not sure this is even possible, but this is how the company wants to see it

 

[JohnDTampaBay]

It may just be when we're inserting rows into the temp table. Change the last part of your code that reads

	ELSE
		SET @End = @Begin
	
	INSERT INTO #Testcomp(Directory,BeginRange,EndRange)
	VALUES(@Cdir, @Begin, @End)

to

	ELSE
	BEGIN
		SET @End = @Begin

		INSERT INTO #Testcomp
			(Directory,BeginRange,EndRange)
		VALUES(@Cdir, @Begin, @End)
	END

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[Compkitty]

That doesn't bring in all the values from the table...

 

[JohnDTampaBay]

What does it return? Seems like you only want to insert into #TestComp when the Directory changes.

I think we may still be dealing with an ORDER BY issue. There is no such thing as first, last, or sequential in a SQL Server recordset unless ORDER BY is included in the SELECT. It may appear in order, but in actuality there is no guarantee unless ORDER BY is specified.

Is there another column in the table - maybe an identity column - that you could ORDER BY to ensure the cursor rows are retrieved in the order you want?


--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)