Multiple zips in extractions

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

Let me try. And I just came up with this (and it works, not a smart way to write SQL, however):

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

From: Tessitura Development Forum [mailto:forums-development@tessituranetwork.com] On Behalf Of Matthew Hoyt
Sent: Tuesday, May 12, 2015 5:07 PM
To: Joe Hsu
Subject: Re: [Tessitura Development Forum] Multiple zips in extractions

Hi Joe,

Here's an example of how to get more than one zip code via a manual edit. Does this help?

select distinct customer_no
from dbo.VS_ADDRESS where LEFT(postal_code,5) in ('63124','63116') and primary_ind = 'Y'

Matt

Your code definitely works! But I think it picked up invalid addresses as well – not a big deal for the the project I am doing for the time being.

THANK YOU SO MUCH for teaching me this.

I have to really sit down and learn SQL.

From: Tessitura Development Forum [mailto:forums-development@tessituranetwork.com] On Behalf Of Matthew Hoyt
Sent: Tuesday, May 12, 2015 5:07 PM
To: Joe Hsu
Subject: Re: [Tessitura Development Forum] Multiple zips in extractions

Hi Joe,

Here's an example of how to get more than one zip code via a manual edit. Does this help?

select distinct customer_no
from dbo.VS_ADDRESS where LEFT(postal_code,5) in ('63124','63116') and primary_ind = 'Y'

Matt

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!

Say someone has an inactive address at zip 10013 but current address is other zip, the constituent record still gets included in this SQL code.

From: Tessitura Development Forum [mailto:forums-development@tessituranetwork.com] On Behalf Of Matthew Hoyt
Sent: Tuesday, May 12, 2015 5:53 PM
To: Joe Hsu
Subject: RE: [Tessitura Development Forum] Multiple zips in extractions

Hey Joe,

So glad you could get where you need to go (more or less)! What kind of invalid addresses are you getting? 

Lucie, nice job explaining how to work the manual edit!

You can get really fancy when pulling addresses.

Here’s a query from an extraction segment that looks for people who have tickets in a few seasons, and who are on a particular list, and who have an address that’s valid for mailing on a particular date, where that address is located in one of the counties near or next to the theaters where we perform:

Select Distinct a.customer_no

 From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock)

JOIN VS_TCK_HIST e (Nolock) ON e.customer_no = a.customer_no

JOIN T_LIST_CONTENTS f (Nolock) ON f.customer_no = a.customer_no

cross apply FT_GET_ADDRESS('5/13/2015',null,null,a.customer_no) b

Where IsNull(a.inactive, 1) = 1

 AND e.season in (67,75,82)

AND f.list_no = 46084

AND b.address_no in (select address_no from VS_ADDRESS where inactive = 'N' and geo_area in (7,8,9,10))

The FT_GET_ADDRESS() function is great when you’re trying to limit list pulls to certain locations or purposes. It pulls address based on four parameters—mailing date, mail purpose, label, and customer_no. (We’re still on Tess version 11. This would have to have been updated for version 12 to account for expanded mail purposes.)

Lucie

Hi Joe,

FT_GET_ADDRESS should get rid of inactive addresses, as should using primary_ind = 'Y' and/or inactive = ‘N’ when referencing VS_ADDRESS. FYI, FT_GET_ADDRESS will pull addresses for affiliates of households based on the household record if the affiliates themselves don’t have an address record (via the inheritance concept). Referencing VS_ADDRESS will not pull affiliates of households unless the affiliates themselves have qualifying criteria.

Matt

You can get really fancy when pulling addresses.

Here’s a query from an extraction segment that looks for people who have tickets in a few seasons, and who are on a particular list, and who have an address that’s valid for mailing on a particular date, where that address is located in one of the counties near or next to the theaters where we perform:

Select Distinct a.customer_no

 From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock)

JOIN VS_TCK_HIST e (Nolock) ON e.customer_no = a.customer_no

JOIN T_LIST_CONTENTS f (Nolock) ON f.customer_no = a.customer_no

cross apply FT_GET_ADDRESS('5/13/2015',null,null,a.customer_no) b

Where IsNull(a.inactive, 1) = 1

 AND e.season in (67,75,82)

AND f.list_no = 46084

AND b.address_no in (select address_no from VS_ADDRESS where inactive = 'N' and geo_area in (7,8,9,10))

The FT_GET_ADDRESS() function is great when you’re trying to limit list pulls to certain locations or purposes. It pulls address based on four parameters—mailing date, mail purpose, label, and customer_no. (We’re still on Tess version 11. This would have to have been updated for version 12 to account for expanded mail purposes.)

Lucie

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!

Thank you I will give it a try.

Your help is truly much appreciated Matt!

Hi Joe,

FT_GET_ADDRESS should get rid of inactive addresses, as should using primary_ind = 'Y' and/or inactive = ‘N’ when referencing VS_ADDRESS. FYI, FT_GET_ADDRESS will pull addresses for affiliates of households based on the household record if the affiliates themselves don’t have an address record (via the inheritance concept). Referencing VS_ADDRESS will not pull affiliates of households unless the affiliates themselves have qualifying criteria.

Matt

You can get really fancy when pulling addresses.

Here’s a query from an extraction segment that looks for people who have tickets in a few seasons, and who are on a particular list, and who have an address that’s valid for mailing on a particular date, where that address is located in one of the counties near or next to the theaters where we perform:

Select Distinct a.customer_no

 From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock)

JOIN VS_TCK_HIST e (Nolock) ON e.customer_no = a.customer_no

JOIN T_LIST_CONTENTS f (Nolock) ON f.customer_no = a.customer_no

cross apply FT_GET_ADDRESS('5/13/2015',null,null,a.customer_no) b

Where IsNull(a.inactive, 1) = 1

 AND e.season in (67,75,82)

AND f.list_no = 46084

AND b.address_no in (select address_no from VS_ADDRESS where inactive = 'N' and geo_area in (7,8,9,10))

The FT_GET_ADDRESS() function is great when you’re trying to limit list pulls to certain locations or purposes. It pulls address based on four parameters—mailing date, mail purpose, label, and customer_no. (We’re still on Tess version 11. This would have to have been updated for version 12 to account for expanded mail purposes.)

Lucie

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!

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!