• Replies 1 reply
  • Subscribers 331 subscribers
  • Views 58 views
  • Users 0 members are here
Related

Dynamic list of DMA zip codes

Anh Le
$organization
Hi everyone,
 
I’m attempting to create a dynamic list of zipcodes but running into issues with exactly how and whether this is possible in Tessitura.
 
I’d like to have a daily refreshing list of any patrons in our database who live within 50 miles of our theater. That’s approximately 210 zipcodes but I can only thing of how to do this as an extraction (with each zipcode as its own criteria), not as a dynamic list in List Manager.
 
Has anyone come up with a solution for how to do this? Thanks in advance for any insight you can share!
 
Anh Le | Director of Marketing & PR
Opera Theatre of Saint Louis | ExperienceOpera.org
ale@opera-stl.org | (314) 963-4294
 
A picture containing shape Description automatically generated
 
 
Parents
  • $organization
    Hey Anh,
     
    This one is tricky, but you can do it easily by editing the SQL when you click Show Query. What I do is set up a list looking for just one zip code and then you can change the query a tiny bit to type in a list of zip codes.
     
     
    What it looks like with one zip code:
     
    SELECT DISTINCT a.customer_no
    FROM   V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
           INNER JOIN
           (SELECT a1.customer_no
            FROM   vs_address AS a1 WITH (NOLOCK)
            WHERE  a1.postal_code = '66030') AS e
           ON e.customer_no = a.customer_no
    WHERE  a.inactive = 1
     
    After you set up a list with a single postal code you can edit the sql. Click Show Query and then you change the equal sign to the word IN and then you put your zips between single quotes separated by commas, and wrapped in another set of parenthesis:
     
    SELECT DISTINCT a.customer_no
    FROM   V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
           INNER JOIN
           (SELECT a1.customer_no
            FROM   vs_address AS a1 WITH (NOLOCK)
            WHERE  a1.postal_code IN ('66030',’64108’,’64111’)) AS e
           ON e.customer_no = a.customer_no
    WHERE  a.inactive = 1
     
    This would be a nightmare to type out, but there is a way to take a list of zips in excel and turn into a string where it enters the ‘,’ for you over and over.
     
    Hope this helps, Greg
     
     
Reply
  • $organization
    Hey Anh,
     
    This one is tricky, but you can do it easily by editing the SQL when you click Show Query. What I do is set up a list looking for just one zip code and then you can change the query a tiny bit to type in a list of zip codes.
     
     
    What it looks like with one zip code:
     
    SELECT DISTINCT a.customer_no
    FROM   V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
           INNER JOIN
           (SELECT a1.customer_no
            FROM   vs_address AS a1 WITH (NOLOCK)
            WHERE  a1.postal_code = '66030') AS e
           ON e.customer_no = a.customer_no
    WHERE  a.inactive = 1
     
    After you set up a list with a single postal code you can edit the sql. Click Show Query and then you change the equal sign to the word IN and then you put your zips between single quotes separated by commas, and wrapped in another set of parenthesis:
     
    SELECT DISTINCT a.customer_no
    FROM   V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
           INNER JOIN
           (SELECT a1.customer_no
            FROM   vs_address AS a1 WITH (NOLOCK)
            WHERE  a1.postal_code IN ('66030',’64108’,’64111’)) AS e
           ON e.customer_no = a.customer_no
    WHERE  a.inactive = 1
     
    This would be a nightmare to type out, but there is a way to take a list of zips in excel and turn into a string where it enters the ‘,’ for you over and over.
     
    Hope this helps, Greg
     
     
Children
No Data