Please Help with query - SQL Server Forums

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

Transact-SQL (2000)

Please Help with query

Author

Topic

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2006-08-01 : 17:00:58

Hi, i need help with the query below. Please advice if you can.

Create table #master(claim_num varchar(10),PROC_CODE varchar(2),SERVICE_LINE_NUM int)

insert into #master (claim_num,PROC_CODE) values('0000','AA')
insert into #master (claim_num,PROC_CODE) values('0000','BB')
insert into #master (claim_num,PROC_CODE) values('1111','BB')
insert into #master (claim_num,PROC_CODE) values('1111','AA')
insert into #master (claim_num,PROC_CODE) values('1111','CC')

i like to update the service_line_num in the #master table
so the result would look like this:

0000 AA 1
0000 BB 2
1111 BB 1
1111 AA 2
1111 CC 3

Below is my query, it works perfectly . However, if i have
more than 10000 records in #master table. The query would run forever. I wonder if there're a better way to handle this.

THANK you for your helps.
--------------------------------
DECLARE @claimnumber varchar(12)
DECLARE @RowCnt int
DECLARE @i int
DECLARE @proc_code varchar(12)

SELECT * into #tmp2 from #master where 1=2
select distinct Claim_num into #tmp from #master

SELECT TOP 1 @claimnumber = Claim_num from #tmp
SET @RowCnt = @@ROWCOUNT
WHILE @RowCnt <> 0
begin
SET @i = 0
insert into #tmp2 select * from #master where Claim_num = @claimnumber
select top 1 @claimnumber = Claim_num, @proc_code = proc_code from #tmp2
set @rowcnt = @@rowcount
while @rowcnt <> 0
BEGIN
set @i = @i + 1
update #master set service_line_num = @i where Claim_num = @claimnumber and proc_code = @proc_code
delete #tmp2 where Claim_num = @claimnumber and proc_code = @proc_code
select top 1 @claimnumber = Claim_num, @proc_code = proc_code from #tmp2
set @rowcnt = @@rowcount
end

DELETE #tmp where Claim_num = @claimnumber
SELECT TOP 1 @claimnumber = Claim_num from #tmp
set @rowcnt = @@rowcount
end

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-08-01 : 18:00:59

How are you creating your #master table? There is a simple way around it using a sub-query, but it means that your SERVICE_LINE_NUM values will be numbered in the alphabetical order of PROC_CODE.
Can you create your #master table with an identity field, so we can tell in which order the lines were written?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-01 : 18:20:54

[code]-- prepare test data
declare @master table (claim_num varchar(10), proc_code varchar(2), service_line_num int)

insert @master (claim_num, proc_code)
select '0000', 'AA' union all
select '0000', 'BB' union all
select '1111', 'BB' union all
select '1111', 'AA' union all
select '1111', 'CC'

-- do the work
update m
set m.service_line_num = (select count(*) from @master z where z.claim_num = m.claim_num and z.proc_code <= m.proc_code)
from @master m

-- show the data
select * from @master[/code]
Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-01 : 18:45:17

Are you using SQL 2005? If so, then there is an easy way to do this.

- Jeff

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-08-01 : 18:51:38

Peter,

That's what I came up with before I realised that the sequences are out (i.e. the service_line_num is sequenced in the order of insert, not the order of the PROC_CODE column). This is why I asked the question.

Tim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-02 : 01:22:17

quote:
Originally posted by timmy

Peter,

That's what I came up with before I realised that the sequences are out (i.e. the service_line_num is sequenced in the order of insert, not the order of the PROC_CODE column). This is why I asked the question.

Tim

Yes, I read your question. But I had to post it, if tocroi72 just made a typo of the ranking.

Peter Larsson
Helsingborg, Sweden

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2006-08-02 : 11:44:45

Thank Peso so much for the quick query - you have save me 10 hours of running my ugly codes.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-02 : 13:39:11

You're welcome.
Thanks for the feedback.

BTW, how long time ran the update query I posted?

Peter Larsson
Helsingborg, Sweden

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2006-08-02 : 16:04:35

it is very fast - i updated 34827 records and it only runs for 2 seconds.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources