Hi,
I have this following code which doesn't seem to ever stop running! Can you help please?
create table #results
(
col varchar(20),
col1 varchar(20),
Number float)
select distinct channeloftrade
into #COT
from pnownsite dos
create table #temp (col1 varchar(50))
insert into #temp values('Active')
insert into #temp values('Inactive')
insert into #temp values('Total')
insert into #results (col, col1)
select t.col1, c.channeloftrade
from #COT c
cross join #temp t
order by 2,1
declare @COT varchar(50)
create table #COT1 (col1 varchar(50))
-- Create list of COTs that we can delete as and when used in the loop
insert into #COT1
select *
from #COT
while exists (select top 1 * from #COT1)
begin
set @COT = (select top 1 * from #COT1)
declare @Active int, @Inactive int, @Total int
select @Active = count(*)
from
(
select distinct pd.uoid
from pnpricedifferential pd
join pnownproduct op on pd.product = op.uoid
join pnownsite os on op.site = os.uoid
where pd.active = 1
and os.channeloftrade = @COT
) as sq1
select @Inactive = count(*) - @Active
from
(
select distinct pd.uoid
from pnpricedifferential pd
join pnownproduct op on pd.product = op.uoid
join pnownsite os on op.site = os.uoid
where os.channeloftrade = @COT
) as sq1
select @Total = count(*)
from
(
select distinct pd.uoid
from pnpricedifferential pd
join pnownproduct op on pd.product = op.uoid
join pnownsite os on op.site = os.uoid
where os.channeloftrade = @COT
) as sq1
exec(
'update #results
set Number = '+@Active+'
where col = ''Active''
and col1 = '''+@COT+'''
update #results
set Number = '+@Inactive+'
where col = ''Inactive''
and col1 = '''+@COT+'''
update #results
set Number= '+@Total+'
where col = ''Total''
and col1 = '''+@COT+''''
)
delete #COT1 where col1 = @COT
end
select * from #results
order by 1,2
drop table #COT
drop table #COT1
drop table #results
drop table #temp