• Replies 4 replies
  • Subscribers 338 subscribers
  • Views 9 views
  • Users 0 members are here
Related

Finding accounts

Christopher Cuhel
$organization

Hi all,

I was wondering if anyone has any SQL script written to be able to find household accounts that only have one individual account. We are realizing we seem to have a lot of accounts like this and want to clean our date. I couldn't think of an easy way to pull a list to find this data so I thought I'd reach out and see if anyone wrote a script in SQL to do this.

Any and all help and advice is greatly appreciated.

Chris Cuhel

The 5th Avenue Theatre

  • $organization

    Unknown said:

    I was wondering if anyone has any SQL script written to be able to find household accounts that only have one individual account. 

    This should work:


    select af.group_customer_no,count(*)
    from T_AFFILIATION af
    join T_CUSTOMER cu
    on af.group_customer_no = cu.customer_no and cu.cust_type = 7
    group by af.group_customer_no
    having count(*) = 1

  • $organization in reply to Chris Jensen

    Thank you Chris! You are awesome. I'll "buy" you a beer at the neighborhood networking event at conference. :)

  • $organization

    Chris,

     

    See if this would work. 

    You may have to adjust the cust_type value to match the “Household” cust type from TR_CUST_TYPE.

     

    SELECT

     

    Household_no = group_customer_no,

    Num_affiliates = COUNT(individual_customer_no)

     

    FROM T_AFFILIATION aff

    join T_CUSTOMER cust on cust.customer_no = aff.group_customer_no and cust.cust_type=8

     

    GROUP BY group_customer_no   

     

    HAVING count(individual_customer_no) < 2

     

    Todd T

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Christopher Cuhel
    Sent: Tuesday, March 01, 2016 5:51 PM
    To: Todd Tiffany
    Subject: [Tessitura Technical Forum] Finding accounts

     

    Hi all,

    I was wondering if anyone has any SQL script written to be able to find household accounts that only have one individual account. We are realizing we seem to have a lot of accounts like this and want to clean our date. I couldn't think of an easy way to pull a list to find this data so I thought I'd reach out and see if anyone wrote a script in SQL to do this.

    Any and all help and advice is greatly appreciated.

    Chris Cuhel

    The 5th Avenue Theatre




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical 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!

  • $organization in reply to Christopher Cuhel

    Unknown said:

    Thank you Chris! You are awesome. I'll "buy" you a beer at the neighborhood networking event at conference. :)

    You're welcome! See you then. :-)