quote:

---

Begin

Set nocount on

Declare Cursid Cursor for
select partnumber from smc_sales.dbo.sales_history
where partnumber not like '%uia%'
and partnumber not like '%uib%'
and partnumber not like '%ula%'
and partnumber not like '%ulb%'
and partnumber not like '%us'
and partnumber not like '%usl'
and partnumber not like '%xa%'
and partnumber not like '%xus'
and partnumber not like 'uiusp%'
and left(partnumber, 1)<>'*'
and left(partnumber, 1)<>'#'
and left(partnumber, 1) not in ('0','1','2','3','4','5','6','7','8','9')
and left(partnumber, 2)<>'A-'
group by partnumber
order by partnumber

Declare @partnumber varchar(100),
@curCount int,
@patternCount int,
@patternCode varchar(10)

Open cursid
fetch next from cursid into @partnumber
while (@@fetch_status<>-1)
Begin
set @curCount=3

while (@curCount<11)
Begin ---Begin Loop for finding match
select @patternCode=pattern_code, @patternCount=count(pattern_code)
from cc_catalog_smc_3100.dbo.cpc_search_pattern
where search_key like left(@partnumber, @curCount)+'%'
group by pattern_code

if (@patternCount=1)
Begin
--Insert record into table
insert into sales_by_pattern
select @patterncode, sum(totalsalenet), count(quantity), count(invoice), avg(totalsalenet)
from sales_history
where partnumber like left(@partnumber, @curCount)+'%'
group by totalsalenet

set @curCount=11
set @patternCount=0
set @patternCode=''
end
else
Begin
set @patternCount=0
set @patternCode=''
set @curCount=@curCount + 1
end
End ---end loop for finding match

fetch next from cursid into @partnumber
End

deallocate Cursid

---