• Replies 3 replies
  • Subscribers 962 subscribers
  • Views 79 views
  • Users 0 members are here
Related

Donations - accumulated value in households

Martin Keen (Past Member)
$organization

Hello folks,

I'm just wondering if anyone else has solved a problem I'm having trouble resolving! I should start off by mentioning we don't use memberships.

I've been tasked with putting our donors into levels of accumulated giving - households are giving me a headache (possibly of our own making). I need to "roll up" donations so that all donations made by a household and any donations made by a household member are attributed to the household for the purposes of attributing the correct level of giving. As an example:

  • there are donations (totaling $20,000) where the John and Mary Jones household constituent is the Contribution owner
  • there are donations (totaling $3,000) where the John Jones constituent is the Contribution owner - but John Jones is also the initiator of donations where the household is the owner
  • there are donations (totaling $2,500) where Mary Jones is the owner

In the reporting, I want to see the John and Mary Jones household listed as having a total giving of $25,500 but not see John or Mary.

Has anyone else managed this? Either by a report stored procedure or by a custom List Manager criteria? Tessitura support has acknowledged that the out-of-the-box criteria cannot cope with this.

All and any responses are more than welcome!

Martin

Parents
  • $organization

    We have all contributions on the household, but I wrote a query using constituencies to see if I could replace an affiliated individual's customer_no with the household, then applied the logic to contributions. Perhaps this could help.

    Lucie

    declare @cust_type table
    ( customer_no int not null,
    cust_type int not null,
    expanded_cust_no int not null
    )
    insert into @cust_type
    ( customer_no,
    cust_type,
    expanded_cust_no
    )
    select distinct
    a.customer_no,
    a.cust_type,
    case when a.cust_type = ## then a.customer_no --## is id of household customer type
    when a.cust_type = 1 then b.expanded_customer_no --1 is id of individual customer type
    else 0 end
    from t_customer a
    join VS_CUSTOMER_WITH_PRIMARY_AFFILIATES b on b.customer_no = a.customer_no
    where a.customer_no in
    (select customer_no
    from vs_contribution
    where fund_no in (1,2) and --use whatever criteria you are looking for in the where clause
    campaign_no in
    (select campaign_no
    from VS_CAMPAIGN
    where fyear = 2023))
    and a.customer_no <> b.expanded_customer_no

    declare @contribution table
    ( household_customer_no int not null,
    ref_no int not null,
    cont_dt datetime not null,
    recd_amt money not null,
    cont_amt money not null
    )
    insert into @contribution
    ( household_customer_no,
    ref_no,
    cont_dt,
    recd_amt,
    cont_amt
    )
    select distinct
    b.expanded_cust_no,
    a.ref_no,
    a.cont_dt,
    a.recd_amt,
    a.cont_amt
    from VS_CONTRIBUTION a
    join @cust_type b on b.customer_no = a.customer_no
    where a.fund_no in (1,2) and --use whatever criteria you are looking for in the where clause
    a.campaign_no in
    (select campaign_no
    from VS_CAMPAIGN
    where fyear = 2023)
    order by b.expanded_cust_no

    select
    a.household_customer_no,
    b.display_name,
    b.sort_name,
    SUM(a.recd_amt) as 'sum_recd_amt',
    SUM(a.cont_amt) as 'sum_cont_amt'
    from @contribution a
    join FT_CONSTITUENT_DISPLAY_NAME() b on b.customer_no = a.household_customer_no
    group by
    a.household_customer_no,
    b.display_name,
    b.sort_name

Reply
  • $organization

    We have all contributions on the household, but I wrote a query using constituencies to see if I could replace an affiliated individual's customer_no with the household, then applied the logic to contributions. Perhaps this could help.

    Lucie

    declare @cust_type table
    ( customer_no int not null,
    cust_type int not null,
    expanded_cust_no int not null
    )
    insert into @cust_type
    ( customer_no,
    cust_type,
    expanded_cust_no
    )
    select distinct
    a.customer_no,
    a.cust_type,
    case when a.cust_type = ## then a.customer_no --## is id of household customer type
    when a.cust_type = 1 then b.expanded_customer_no --1 is id of individual customer type
    else 0 end
    from t_customer a
    join VS_CUSTOMER_WITH_PRIMARY_AFFILIATES b on b.customer_no = a.customer_no
    where a.customer_no in
    (select customer_no
    from vs_contribution
    where fund_no in (1,2) and --use whatever criteria you are looking for in the where clause
    campaign_no in
    (select campaign_no
    from VS_CAMPAIGN
    where fyear = 2023))
    and a.customer_no <> b.expanded_customer_no

    declare @contribution table
    ( household_customer_no int not null,
    ref_no int not null,
    cont_dt datetime not null,
    recd_amt money not null,
    cont_amt money not null
    )
    insert into @contribution
    ( household_customer_no,
    ref_no,
    cont_dt,
    recd_amt,
    cont_amt
    )
    select distinct
    b.expanded_cust_no,
    a.ref_no,
    a.cont_dt,
    a.recd_amt,
    a.cont_amt
    from VS_CONTRIBUTION a
    join @cust_type b on b.customer_no = a.customer_no
    where a.fund_no in (1,2) and --use whatever criteria you are looking for in the where clause
    a.campaign_no in
    (select campaign_no
    from VS_CAMPAIGN
    where fyear = 2023)
    order by b.expanded_cust_no

    select
    a.household_customer_no,
    b.display_name,
    b.sort_name,
    SUM(a.recd_amt) as 'sum_recd_amt',
    SUM(a.cont_amt) as 'sum_cont_amt'
    from @contribution a
    join FT_CONSTITUENT_DISPLAY_NAME() b on b.customer_no = a.household_customer_no
    group by
    a.household_customer_no,
    b.display_name,
    b.sort_name

Children
No Data