Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Top 20 percent loop

Status
Not open for further replies.

EBOUGHEY

Programmer
Aug 20, 2002
143
US
Hi,

I need to update the top 20 percent records within a 50 mile radius inside the file with an "A" in the keycode field. The catch? It needs to start at the top 20 percent when the dealer # changes.

I haven't really started much because I'm being told the only way is a cursor and I really don't want to do that if possible since the file is huge...

This is what someone sent me to start with....

Any assistance would be greatly appreciated!!!

Elena



DECLARE @dlrnmbr varchar(10)

--declare the cursor which is basically the name of the bucket

DECLARE addDealerSegment CURSOR
FOR

select distinct dlrnmbr
from an_clientdata_temp
where cast(distance as numeric(10,0)) <= 50
order by dlrnmbr



--declare the counter (not needed for cursor but I like to watch it when it runs)

DECLARE @count int
SELECT @count = 1

--this is actually what loads the information to be called

OPEN addDealerSegment
FETCH NEXT FROM addDealerSegment INTO @dlrnmbr

-- this is the beginning of the loop

WHILE (@@fetch_status <> -1)
BEGIN

IF (@@fetch_status <> -2)
BEGIN
PRINT 'inserting records for dealer ' + @dlrnmbr
insert into tbldealersegment
select cast(@dlrnmbr as varchar(10))

END
FETCH NEXT FROM addDealerSegment INTO @dlrnmbr
SELECT @count = @count + 1
END

CLOSE addDealerSegment
DEALLOCATE addDealerSegment
GO
 

try this:

update dealers set dlr_num = 'a' + dlr_num
where dlr_num in
( select top 20 percent dlr_num
from dealers
order by distance )
 
That code didn't work. I had to break down and use the cursor due to time restraints. Below is the working code though. Hopefully someone will be able to utilize it down the line somewhere.

If somebody does know of some way to accomplish the below without a cursor, please let me know. Most of our files are 30 million plus records and cursors take forever to run...

Elena




--here you declare the variables that we are going to store

DECLARE @dlrnmbr varchar(10)



--declare the cursor which is basically the name of the bucket

DECLARE top20 CURSOR

FOR

SELECT distinct dlrnmbr

FROM [68506_dif]

order by dlrnmbr



--declare the counter (not needed for cursor but I like to watch it when it runs)

DECLARE @count int

SELECT @count = 1



--this is actually what loads the information to be called

OPEN top20

FETCH NEXT FROM top20 INTO @dlrnmbr


-- this is the beginning of the loop

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

PRINT 'updating counts for dealer ' + @dlrnmbr

update an_clientdata_temp

set keycode = 'A'

where cast(distance as numeric(10,0)) <= 50 and @dlrnmbr = dlrnmbr

and uniqueid IN (select top 20 percent uniqueid from an_clientdata_temp

where @dlrnmbr = dlrnmbr

and cast(distance as numeric(10,0)) <= 50 )

END

FETCH NEXT FROM top20 INTO @dlrnmbr

SELECT @count = @count + 1

END



CLOSE top20

DEALLOCATE top20

GO
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top