Help optimizing while loop query

[charlise]

Hello Everyone,
I have the following while loop that is taking several hours to run. Does anyone have any suggestions as to how to optimize the query so it will run faster? Currently, I have an index on the #test_data table based on the [ID] field. Any suggestions would be GREATLY appreciated.


declare @counter int
declare @store_counter int

set @counter = 2 --initialize counter to second row variable
set @store_counter = @counter - 1

while @counter <= (select max([ID]) from #test_data) /*While it hasn't looked at every record*/
begin
/*Case when claim is a refill that's on time or early*/
If ((select active from #test_data where [ID] = @counter) =
(select active from #test_data where [ID] = (@counter-1)) and
(select id_number from #test_data where [ID] = @counter) =
(select id_number from #test_data where [ID] = (@counter-1)) and
(select stren from #test_data where [ID] = @counter) =
(select stren from #test_data where [ID] = (@counter-1)) and
(select dateend from #test_data where [ID] = (@counter-1)) >=
(select [date] from #test_data where [ID] = @counter)
and (select floor(max(b.days) * .25)
from (select days from #test_data where [ID] = @counter
union select days from #test_data where [ID] = (@counter-1))b)>=
(select datediff(dd,(select dateend from #test_data where [ID] = (@counter-1)),
(select [date] from #test_data where [ID] = @counter))))
begin
insert into #Count_Dosage
select * from #test_data where [ID] = @counter
and [ID] not in (select [ID] from #count_dosage)
union all
select * from #test_data where [ID] = (@counter - 1)
and [ID] not in (select [ID] from #count_dosage)
--set @store_counter = (select max([ID]) from #count_dosage)
end
Else
/*Case when claim is a late refill*/
If ((select active from #test_data where [ID] = @counter) =
(select active from #test_data where [ID] = @counter - 1) and
(select id_number from #test_data where [ID] = @counter) =
(select id_number from #test_data where [ID] = @counter - 1) and
(select stren from #test_data where [ID] = @counter) =
(select stren from #test_data where [ID] = @counter-1) and
(select dateend from #test_data where [ID] = @counter) >
(select [date] from #test_data where [ID] = @counter-1)
and (select floor(max(b.days) * .15)
from (select days from #test_data where [ID] = @counter
union select days from #test_data where [ID] = @counter-1)b)>=
(select datediff(dd,(select dateend from #test_data where [ID] = @counter-1),
(select [date] from #test_data where [ID] = @counter))))
begin
insert into #Count_Dosage
select * from #test_data where [ID] = @counter
and [ID] not in (select [ID] from #count_dosage)
union all
select * from #test_data where [ID] = (@counter - 1)
and [ID] not in (select [ID] from #count_dosage)
--set @store_counter = (select max([ID]) from #count_dosage)
end
Else
/*Claim is the same patient, drug, or strength but not a refill*/
begin
set @store_counter = @store_counter --Keep the same value to compare to the next claim
end
set @counter = @counter + 1 --increment counter to look at next record at next iteration of loop
end --End while loop

 

[vongrunt]

This query is VERY redundant - do you want to simplify it first?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[charlise]

I think I've figured out what I need to do. Thanks!

 

[vongrunt]

If you say so ... based on my banzai observarions this code can be reduced down 50%, then further 1/3... and maybe modify to work without loops (depending on logic used to fill #count_dosage table).

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]