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!

I'm trying to add a Constituency End Date to the list criteria so I can pull a list of people who have expired Subs constituencies. I have Constituency Start Date but no End Date.  I have added the Constituency End Date to the T_Keyword table.

I have tx_const_cust as the Detail Tbl. !.end_date as the Detail Col. 

I did find a message from Kevin that said make sure the new entry references the view not the table but I tried vxs_const_cust and I got an error message. I switched it to tx_const_cust and got the same error message. What am I doing wrong? 

Thx in advance. 

Not sure if I'm adding to the prior posts, but this query works for me-

SELECT distinct
a.customer_no
FROM TX_CONST_CUST a
WHERE a.constituency=77 and a.end_dt<'2014-9-1'

It catches all people with a constituency that ends prior to Sept 1 2014.

Megan

Not sure if I'm adding to the prior posts, but this query works for me-

SELECT distinct
a.customer_no
FROM TX_CONST_CUST a
WHERE a.constituency=77 and a.end_dt<'2014-9-1'

It catches all people with a constituency that ends prior to Sept 1 2014.

Megan