• Replies 9 replies
  • Subscribers 337 subscribers
  • Views 88 views
  • Users 0 members are here
Related

List Question: Replace Household with A1

Lauren Gruber
$organization

Hi all,

I'm working on a somewhat unique data pull and in cases where a household qualifies under the criteria I have set, I'd like the household to be replaced with just the A1 affiliate of that household (in other words, if the John and Jane Smith Household qualifies, I'd like my results to list John's individual record only). Essentially, I'd like to do the opposite of the "Replace individual constituents with their primary households, if one exists" functionality that List Manager offers. This is something that I am happy to do either in List Manager or in a SQL query, so ideas from either avenue are welcome. Thanks for any insight you can provide!

Lauren

Parents
  • $organization

    Hi Lauren,

    I did something similar the other day when I needed to have columns for each name on a household record.  I did this in Management Studio and you mentioned you could do this with SQL so sharing what i did.  I first created a temp table of the accounts on a list.  I then created a 2nd temp table and inserted into this the customer_no, and if it was a HH i put in the A1 name in one column and the A2 name in another, as well as a column for the individual.  

    select t.customer_no, c.cust_type,
    max (case when c.cust_type= 1 then t.customer_no else null end) as IND,
    Max(CASE when c.cust_type = 9 and af.name_ind in ( -1)
    then af.individual_customer_no else null end) as 'HHA1'   ----also had case statement for the A2 name which you could add

    into #TempAcctNos
    from #Temp t
    join T_CUSTOMER c on t.customer_no = c.customer_no
    left join T_AFFILIATION af on t.customer_no = af.group_customer_no
    group by t.customer_no, c.cust_type
    order by t.customer_no

    From this I pulled in the names from t_customer for each of the columns:  IND, HHA1 and HHA2.  Below is code i used for pulling in the A1 names.  To pull in IND, change ta.HHA1 to ta.IND and the cust type to 1.

    select ta.customer_no, c.fname, c.mname, c.lname, s.description
    from #TempAcctNos ta
    join T_CUSTOMER c on ta.HHA1 = c.customer_no
    join TR_SUFFIX s on c.suffix = s.id
    where ta.cust_type =9

    Hope this gets you on your way.

    T.C.

    Pittsburgh Cultural Trust Consortium

Reply
  • $organization

    Hi Lauren,

    I did something similar the other day when I needed to have columns for each name on a household record.  I did this in Management Studio and you mentioned you could do this with SQL so sharing what i did.  I first created a temp table of the accounts on a list.  I then created a 2nd temp table and inserted into this the customer_no, and if it was a HH i put in the A1 name in one column and the A2 name in another, as well as a column for the individual.  

    select t.customer_no, c.cust_type,
    max (case when c.cust_type= 1 then t.customer_no else null end) as IND,
    Max(CASE when c.cust_type = 9 and af.name_ind in ( -1)
    then af.individual_customer_no else null end) as 'HHA1'   ----also had case statement for the A2 name which you could add

    into #TempAcctNos
    from #Temp t
    join T_CUSTOMER c on t.customer_no = c.customer_no
    left join T_AFFILIATION af on t.customer_no = af.group_customer_no
    group by t.customer_no, c.cust_type
    order by t.customer_no

    From this I pulled in the names from t_customer for each of the columns:  IND, HHA1 and HHA2.  Below is code i used for pulling in the A1 names.  To pull in IND, change ta.HHA1 to ta.IND and the cust type to 1.

    select ta.customer_no, c.fname, c.mname, c.lname, s.description
    from #TempAcctNos ta
    join T_CUSTOMER c on ta.HHA1 = c.customer_no
    join TR_SUFFIX s on c.suffix = s.id
    where ta.cust_type =9

    Hope this gets you on your way.

    T.C.

    Pittsburgh Cultural Trust Consortium

Children