Pulling a Constituency

Thanks,

 

Marie

__

Marie Kocher | Development Assistant

Kansas City Ballet

Todd Bolender Center for Dance & Creativity

500 W Pershing Rd

Kansas City, MO 64108

816-931-2232 ext 1382 | 816-931-1172 (fax)

mkocher@kcballet.org

 

Buy tickets or enroll for classes: www.kcballet.org

From: Tessitura Development Forum [forums-development@tessituranetwork.com] on behalf of Matthew Echert [bounce-matthewechert5584@tessituranetwork.com]
Sent: Wednesday, August 26, 2015 5:26 PM
To: Marie Kocher
Subject: Re: [Tessitura Development Forum] Pulling a Constituency

Hi Marie,

You can accomplish this with a couple of manual edits to your list query. If you're comfortable doing that, it's relatively simple. I built a simple list with only "Constituency in..." and then clicked into Manual Edit and made the changes below. (If you're not on v12 it may look a little different but I believe the same approach should work.)

Edits:

 

 

• Replace vxs_const_cust with tx_const_cust 
• Add this text: and a1.start_dt >= '2015/08/31'

Result:

Select Distinct a.customer_no
From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)
JOIN (

Select a1.customer_no
From tx_const_cust a1 WITH (NOLOCK) Where a1.constituency in (11)
  and a1.start_dt >= '2015/08/31'

) as e ON e.customer_no = a.customer_no
Where  IsNull(a.inactive, 1) = 1

"11" is the constituency I chose, so that will be different in your list depending on which constituency(s) you select. This same technique works for looking at constituencies past their end dates. You could change the above to this, for example:

Select Distinct a.customer_no 
From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)
JOIN (

Select a1.customer_no
From tx_const_cust a1 WITH (NOLOCK) Where a1.constituency in (11)
  and a1.start_dt >= '2013/08/31'
  and a1.end_dt <= '2014/08/30 23:59:59' 

) as e ON e.customer_no = a.customer_no
Where  IsNull(a.inactive, 1) = 1

 

If this is something you have to do often, you might want to look into having your DBA build some custom list criteria to save time in the future. I don't think it would be terribly difficult.

Hope that helps!

Hello all, 

We add a "Academy Student" constituency to all of our Ballet School Student records. The start date for the constituency is the first day of classes, Aug 31 - thus, a future date from today.

I need to pull a list of these students today, however my list returns no records. 

How can I pull a constituency with a future date? Furthermore, how can I pull a constituency with a past end date?

Thanks, 

Marie Kocher

Development Assistant

Kansas City Ballet

Todd Bolender Center for Dance & Creativity

500 W Pershing Rd

Kansas City, MO 64108

w 816.931.2232 | d 816.216.5582

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!

Thanks,

 

Marie

__

Marie Kocher | Development Assistant

Kansas City Ballet

Todd Bolender Center for Dance & Creativity

500 W Pershing Rd

Kansas City, MO 64108

816-931-2232 ext 1382 | 816-931-1172 (fax)

mkocher@kcballet.org

 

Buy tickets or enroll for classes: www.kcballet.org

From: Tessitura Development Forum [forums-development@tessituranetwork.com] on behalf of Matthew Echert [bounce-matthewechert5584@tessituranetwork.com]
Sent: Wednesday, August 26, 2015 5:26 PM
To: Marie Kocher
Subject: Re: [Tessitura Development Forum] Pulling a Constituency

Hi Marie,

You can accomplish this with a couple of manual edits to your list query. If you're comfortable doing that, it's relatively simple. I built a simple list with only "Constituency in..." and then clicked into Manual Edit and made the changes below. (If you're not on v12 it may look a little different but I believe the same approach should work.)

Edits:

 

 

• Replace vxs_const_cust with tx_const_cust 
• Add this text: and a1.start_dt >= '2015/08/31'

Result:

Select Distinct a.customer_no
From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)
JOIN (

Select a1.customer_no
From tx_const_cust a1 WITH (NOLOCK) Where a1.constituency in (11)
  and a1.start_dt >= '2015/08/31'

) as e ON e.customer_no = a.customer_no
Where  IsNull(a.inactive, 1) = 1

"11" is the constituency I chose, so that will be different in your list depending on which constituency(s) you select. This same technique works for looking at constituencies past their end dates. You could change the above to this, for example:

Select Distinct a.customer_no 
From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)
JOIN (

Select a1.customer_no
From tx_const_cust a1 WITH (NOLOCK) Where a1.constituency in (11)
  and a1.start_dt >= '2013/08/31'
  and a1.end_dt <= '2014/08/30 23:59:59' 

) as e ON e.customer_no = a.customer_no
Where  IsNull(a.inactive, 1) = 1

 

If this is something you have to do often, you might want to look into having your DBA build some custom list criteria to save time in the future. I don't think it would be terribly difficult.

Hope that helps!

Hello all, 

We add a "Academy Student" constituency to all of our Ballet School Student records. The start date for the constituency is the first day of classes, Aug 31 - thus, a future date from today.

I need to pull a list of these students today, however my list returns no records. 

How can I pull a constituency with a future date? Furthermore, how can I pull a constituency with a past end date?

Thanks, 

Marie Kocher

Development Assistant

Kansas City Ballet

Todd Bolender Center for Dance & Creativity

500 W Pershing Rd

Kansas City, MO 64108

w 816.931.2232 | d 816.216.5582

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!