Best Practice for Zip Code Criteria

I'm sure there are a million ways to get at this in Tessitura but this is how I do it using the manual edit function on segment criteria in List Manager and Extraction Manager. For the five digit zip code: Typically this is in an extraction so I do an exclusion segment of NOT IN and the desired codes so that it excludes everyone in the database not having those zip codes. That way all of the inclusion segments below in my extraction are not manual and have various levels of unrelated and sometimes complex criteria. You could obviously do this as an IN if you just want a simple list of just those zip codes. Select Distinct a.customer_no From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock) JOIN vs_address e (Nolock) ON e.customer_no = a.customer_no Where 1 = 1 AND e.primary_ind in ('Y') AND LEFT(postal_code,5) not in ('10026','10027') Usually we isolate geography by the less granular three digit SCF and here's the code for that., again with the NOT IN but it could be reversed for a quick list. Select Distinct a.customer_no From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock) JOIN vs_address e (Nolock) ON e.customer_no = a.customer_no Where 1 = 1 AND e.primary_ind in ('Y') AND LEFT(postal_code,3) not in ('063','064','065','066','067','068','069','070','071','072','073','074','075','076','077','078','079','085','086','087','088','089','100','101','102','103','104','105','106','107','108','109','110','111','112','113','114','115','116','117','118','119') Let me know if you have any questions. Chuck Buchanan

Let me try that again without the crazy formatting that just happend: I'm sure there are a million ways to get at this in Tessitura but this is how I do it using the manual edit function on segment criteria in List Manager and Extraction Manager. For the five digit zip code: Typically this is in an extraction so I do an exclusion segment of NOT IN and the desired codes so that it excludes everyone in the database not having those zip codes. That way all of the inclusion segments below in my extraction are not manual and have various levels of unrelated and sometimes complex criteria. You could obviously do this as an IN if you just want a simple list of just those zip codes. Select Distinct a.customer_no From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock) JOIN vs_address e (Nolock) ON e.customer_no = a.customer_no Where 1 = 1 AND e.primary_ind in ('Y') AND LEFT(postal_code,5) not in ('10026','10027') Usually we isolate geography by the less granular three digit SCF and here's the code for that., again with the NOT IN but it could be reversed for a quick list. Select Distinct a.customer_no From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock) JOIN vs_address e (Nolock) ON e.customer_no = a.customer_no Where 1 = 1 AND e.primary_ind in ('Y') AND LEFT(postal_code,3) not in ('100','101','102','103','104','105') Let me know if you have any questions. Chuck Buchanan

Let me try that again without the crazy formatting that just happend: I'm sure there are a million ways to get at this in Tessitura but this is how I do it using the manual edit function on segment criteria in List Manager and Extraction Manager. For the five digit zip code: Typically this is in an extraction so I do an exclusion segment of NOT IN and the desired codes so that it excludes everyone in the database not having those zip codes. That way all of the inclusion segments below in my extraction are not manual and have various levels of unrelated and sometimes complex criteria. You could obviously do this as an IN if you just want a simple list of just those zip codes. Select Distinct a.customer_no From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock) JOIN vs_address e (Nolock) ON e.customer_no = a.customer_no Where 1 = 1 AND e.primary_ind in ('Y') AND LEFT(postal_code,5) not in ('10026','10027') Usually we isolate geography by the less granular three digit SCF and here's the code for that., again with the NOT IN but it could be reversed for a quick list. Select Distinct a.customer_no From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock) JOIN vs_address e (Nolock) ON e.customer_no = a.customer_no Where 1 = 1 AND e.primary_ind in ('Y') AND LEFT(postal_code,3) not in ('100','101','102','103','104','105') Let me know if you have any questions. Chuck Buchanan