Ver11 Affiliation Finder

Hey All,

So, in thinking about the new affiliation ability of ver11 I've come up with some code to find potential affiliates that haven't been identified. I was wondering if I could get a little feedback on what everyone thinks!

Normal 0 false false false EN-CA X-NONE X-NONE MicrosoftInternetExplorer4

Normal 0 false false false EN-CA X-NONE X-NONE MicrosoftInternetExplorer4

use impresario

 

create table #addycount (

addy varchar(64),

cnt int)

 

--* Find duplicate addresses *--

 

insert into #addycount (addy, cnt)

select ad.street1, COUNT(cust.customer_no)

from T_CUSTOMER cust

join T_ADDRESS ad on cust.customer_no = ad.customer_no

--* Remove inactive addresses, merged and affiliated accounts *--

where ad.inactive <> 'Y'/* Inactive and merged

and ad.customer_no not in (select aff.individual_customer_no from T_AFFILIATION aff where aff.inac

= 'N')

group by ad.street1

having count(cust.customer_no) > 1

 

select * from #addycount

 

--* Find customer info associated with those addresses *--

 

select cust.customer_no, cust.fname, cust.lname, ad.street1 from T_CUSTOMER cust

join t_address ad on cust.customer_no = ad.customer_no

where ad.street1 in (select addy from #addycount)

order by ad.street1

 

drop table #addycount