Multiple zips in extractions

Hi Lucie,

Thank you, I tried your SQL code as well and it works well

I actually came up with something stupid but it also worked:

Select Distinct a.customer_no

 From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)

JOIN (Select a1.customer_no From vs_address a1 WITH (NOLOCK) Where a1.postal_code = '94027') as e ON e.customer_no = a.expanded_customer_no

Where  IsNull(a.inactive, 1) = 1

UNION

Select Distinct a.customer_no

 From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)

JOIN (Select a1.customer_no From vs_address a1 WITH (NOLOCK) Where a1.postal_code = '11962') as e ON e.customer_no = a.customer_no

Where  IsNull(a.inactive, 1) = 1

UNION

Select Distinct a.customer_no

 From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)

JOIN (Select a1.customer_no From vs_address a1 WITH (NOLOCK) Where a1.postal_code = '10013') as e ON e.customer_no = a.customer_no

Where  IsNull(a.inactive, 1) = 1

UNION

Select Distinct a.customer_no

 From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)

JOIN (Select a1.customer_no From vs_address a1 WITH (NOLOCK) Where a1.postal_code = '10065') as e ON e.customer_no = a.customer_no

Where  IsNull(a.inactive, 1) = 1

UNION

Select Distinct a.customer_no

 From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)

JOIN (Select a1.customer_no From vs_address a1 WITH (NOLOCK) Where a1.postal_code = '10075') as e ON e.customer_no = a.customer_no

Where  IsNull(a.inactive, 1) = 1

In List Manager, if you select a 5-digit postal code as your sole criterion (Postal Code = 33133), then click on the Manual Edit button, you should see something like this:

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  IsNull(a.inactive, 1) = 1

 AND e.postal_code = '33133'

That takes care of all addresses that have the exact postal code “33133” but misses the zip plus 4’s. So edit the last line of the code to look for the first 5 digits only, and use “in” followed by parentheses including all the 5-digit postal codes that you are interested in. Note that the postal codes have single quotations around them, because they are, for the purposes of the code, text strings, not numbers:

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  IsNull(a.inactive, 1) = 1

 AND substring(e.postal_code,1,5) in ('33133','33134','33143','33146','33156','33158')

I’m sure there’s an easier way to do this, but this works.

Lucie

______________________________
Lucie Spieler
IT Development and Training Manager

Florida Grand Opera

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Development Forum. You may reply to this message to post to the Development forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Hi Lucie,

Thank you, I tried your SQL code as well and it works well

I actually came up with something stupid but it also worked:

Select Distinct a.customer_no

 From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)

JOIN (Select a1.customer_no From vs_address a1 WITH (NOLOCK) Where a1.postal_code = '94027') as e ON e.customer_no = a.expanded_customer_no

Where  IsNull(a.inactive, 1) = 1

UNION

Select Distinct a.customer_no

 From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)

JOIN (Select a1.customer_no From vs_address a1 WITH (NOLOCK) Where a1.postal_code = '11962') as e ON e.customer_no = a.customer_no

Where  IsNull(a.inactive, 1) = 1

UNION

Select Distinct a.customer_no

 From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)

JOIN (Select a1.customer_no From vs_address a1 WITH (NOLOCK) Where a1.postal_code = '10013') as e ON e.customer_no = a.customer_no

Where  IsNull(a.inactive, 1) = 1

UNION

Select Distinct a.customer_no

 From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)

JOIN (Select a1.customer_no From vs_address a1 WITH (NOLOCK) Where a1.postal_code = '10065') as e ON e.customer_no = a.customer_no

Where  IsNull(a.inactive, 1) = 1

UNION

Select Distinct a.customer_no

 From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)

JOIN (Select a1.customer_no From vs_address a1 WITH (NOLOCK) Where a1.postal_code = '10075') as e ON e.customer_no = a.customer_no

Where  IsNull(a.inactive, 1) = 1

In List Manager, if you select a 5-digit postal code as your sole criterion (Postal Code = 33133), then click on the Manual Edit button, you should see something like this:

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  IsNull(a.inactive, 1) = 1

 AND e.postal_code = '33133'

That takes care of all addresses that have the exact postal code “33133” but misses the zip plus 4’s. So edit the last line of the code to look for the first 5 digits only, and use “in” followed by parentheses including all the 5-digit postal codes that you are interested in. Note that the postal codes have single quotations around them, because they are, for the purposes of the code, text strings, not numbers:

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  IsNull(a.inactive, 1) = 1

 AND substring(e.postal_code,1,5) in ('33133','33134','33143','33146','33156','33158')

I’m sure there’s an easier way to do this, but this works.

Lucie

______________________________
Lucie Spieler
IT Development and Training Manager

Florida Grand Opera

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Development Forum. You may reply to this message to post to the Development forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!