• Replies 3 replies
  • Subscribers 209 subscribers
  • Views 38 views
  • Users 0 members are here
Related

Extractions and constituencies- beginner question

Former Member
Former Member $organization

Forgive me, our org has no database administrator. I am putting this in the Education field because we rely heavily on education constituencies to track our artists and I would like to be sure I can report accurately.

I would like to build an extraction set using constituencies as my criteria. Example: I want to find all people with a committee member constituency end date before 6/30/12. Here is what my criteria looks like:

Constituency end date <= 06/30/2012   (AND)

Constituency has 10 values selected...(whatever the constituency is)

Right now our database only pulls current constituencies (no end date). For instance, this extraction is pulling up one person that has a start date of a committee constituency on 1/4/12.  I have gone through the extraction videos and I don't think I'm doing anything wrong. Perhaps someone out there would disagree or have  some suggestions?

Thanks for your time,

Halley

Parents
  • $organization
    • Go back to List Builder
    • Create a new list
    • Select Constituency from the Constituent folder & select all the constituencies you want to look at.
    • Press the "Manual Edit" button at the very bottom right of the screen.
    • Enter the text in purple at the end of the text field:


    Select Distinct a.customer_no
     From t_customer a (NOLOCK)
    JOIN vxs_const_cust e (NOLOCK) ON a.customer_no = e.customer_no
     Where  IsNull(a.inactive, 1) = 1
     AND e.constituency in  (33)
     AND e.end_dt >= '2012-06-30 00:00:00.000'
     AND e.end_dt <= '2012-06-30 23:59:59.999'



    [edited by: Sarah Parker at 2:45 PM (GMT -6) on 5 Dec 2012]
Reply
  • $organization
    • Go back to List Builder
    • Create a new list
    • Select Constituency from the Constituent folder & select all the constituencies you want to look at.
    • Press the "Manual Edit" button at the very bottom right of the screen.
    • Enter the text in purple at the end of the text field:


    Select Distinct a.customer_no
     From t_customer a (NOLOCK)
    JOIN vxs_const_cust e (NOLOCK) ON a.customer_no = e.customer_no
     Where  IsNull(a.inactive, 1) = 1
     AND e.constituency in  (33)
     AND e.end_dt >= '2012-06-30 00:00:00.000'
     AND e.end_dt <= '2012-06-30 23:59:59.999'



    [edited by: Sarah Parker at 2:45 PM (GMT -6) on 5 Dec 2012]
Children
  • $organization in reply to Sera Cocora

    Sorry I just reread your question. The code I gave you before gives you anyone with an expiration date on 6.30.2012 only. For anyone with expiration date before or on 6.30.2012 use this:

    Select Distinct a.customer_no
     From t_customer a (NOLOCK)
    JOIN vxs_const_cust e (NOLOCK) ON a.customer_no = e.customer_no
     Where  IsNull(a.inactive, 1) = 1
     AND e.constituency in  (the constituent numbers you selected will automatically populate here)
     AND e.end_dt <= '2012-06-30 23:59:59.999'



    [edited by: Sarah Parker at 7:34 PM (GMT -6) on 28 Nov 2012]