Duplicate Records Count - 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

General SQL Server Forums

New to SQL Server Programming

Duplicate Records Count

Author

Topic

tammy2512
Starting Member

8 Posts

Posted - 2010-08-20 : 20:10:31

Hello,

Any help or advice is really appreciated

I have the following table

Table A
vendorname

walmart
walmartinc
walmartinc
cubfoods
cubfoods
cubfood
asper
apser

I need the view the count of the duplicated vendors. if I perform the following

select vendorname,count(*) as duplicates from vendors
group by vendorname having count(*) >1

the following o/p is displayed

Vendorname duplicates
walmartinc 2
cubfoods 2

As we can see that (walmart, walmart inc),(asper,apser) are the same vendors; but because of the extra character or number its been considered unique; and because of which I am unable to specify the correct number of unique and duplicate vendors in the vendor table of nearly 15000 records.

please advice me how can i avoid this problem; is there a way to compare two columns based on the first 7 characters.

Thank you

Devart
Posting Yak Master

102 Posts

Posted - 2010-08-21 : 03:31:46

Hello,

You can try this:

declare @vendor table (vendorname varchar(7),vendorchr int,vendorlen int,_count int,lvendor varchar(max))

insert into @vendor (vendorname,vendorchr,vendorlen,_count)
select
vendorname,vendorchr,vendorlen,count(*)
from
(
select
left(Vendorname,7) as vendorname,
ascii(left(Vendorname,1))+ascii(left(Vendorname,2))+ascii(left(Vendorname,3))+ascii(left(Vendorname,4))+ascii(left(Vendorname,5))+ascii(left(Vendorname,6))+ascii(left(Vendorname,7)) as vendorchr,
datalength(left(Vendorname,7)) as vendorlen
from <YOUR_TABLE_NAME> v
where <YOUR_TABLE_NAME>
( select count(*)
from
where ascii(left(v.Vendorname,1))+ascii(left(v.Vendorname,2))+ascii(left(v.Vendorname,3))+ascii(left(v.Vendorname,4))+ascii(left(Vendorname,5))+ascii(left(Vendorname,6))+ascii(left(Vendorname,7))=ascii(left(Vendorname,1))+ascii(left(Vendorname,2))+ascii(left(v.Vendorname,3))+ascii(left(v.Vendorname,4))+ascii(left(v.Vendorname,5))+ascii(left(v.Vendorname,6))+ascii(left(v.Vendorname,7)) and
datalength(left(Vendorname,7))=datalength(left(v.Vendorname,7)))>1
) ss
group by
vendorname,vendorchr,vendorlen

declare curs cursor read_only forward_only
for select vendorname,vendorchr,vendorlen from @vendor order by vendorchr,vendorlen,vendorname

declare @vm varchar(7)
declare @vc int
declare @vl int

declare @vc_c int
declare @vl_c int
declare @sql varchar(max)

open curs
fetch next from curs into @vm,@vc,@vl

set @vc_c=@vc
set @vl_c=@vl
set @sql='';
while @@fetch_status=0
begin
if @vc_c<>@vc and @vl_c<>@vl and datalength(@sql)>0
begin
update @vendor set lvendor=replace('~'+@sql,'~; ','') where vendorchr=@vc_c and vendorlen=@vl_c
set @sql='';
end
else
begin
set @sql=@sql+'; '+@vm;
end
set @vc_c=@vc
set @vl_c=@vl
fetch next from curs into @vm,@vc,@vl
end

close curs
deallocate curs

select
vendorname,
sum(_count) as vendor_count
from (select isnull(lvendor,vendorname) as vendorname,_count from @vendor) s
group by vendorname

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-21 : 04:03:48

whats the rule for grouping then? if you're same pattern coming in more than 1 rows, you need to group them as one?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources