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
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
This is good. Postal code would be a useful addition, and perhaps match on just part of street1 (like a dupe id script).
Hi Chris: Not a bad idea. I'll tinker with it and let everyone know what I come up with...