• Replies 2 replies
  • Subscribers 412 subscribers
  • Views 72 views
  • Users 0 members are here
Related

Tracking number of gifts over 10 years

Sara Swain
$organization

Hello, All-

Has anyone had experience pulling the number of gifts a donor has contributed over a given time frame? I'm specifically looking to pull a list of donors who have given at least 7 times during the last 10 years, and I haven't been able to come up with a method of doing this that isn't super labor-intensive.

Thanks for any feedback you can offer!

Sara Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

Parents
  • $organization

    It is fairly easy if you know how to execute the SQL

    All donations, including 0 values:

    select customer_no, COUNT(*) from t_contribution where cont_dt > dateadd(YEAR, -10, GETDATE()) group by customer_no having COUNT(*)>= 7;

    All donations, not including 0 values:

    select customer_no, COUNT(*) from t_contribution where cont_amt > 0 and cont_dt > dateadd(YEAR, -10, GETDATE()) group by customer_no having COUNT(*)>= 7;

     

    Aggregate subquery, if you want to include creditees:

    select customer_no, cont_amt, cont_dt from T_CONTRIBUTION

    union all

    select creditee_no, credit_amt, cont_dt from T_CREDITEE join T_CONTRIBUTION on T_CONTRIBUTION.ref_no = T_CREDITEE.ref_no

     

    Apologies if I got anything wrong but I'm just showing these as examples. Outright I will tell you this is based on Contribution amount and not Rec'd Amount. Also you should probably remove the anon customer 0 which will most likely be included in this list.

    You can run these against your Impresario DB in SQL Server Manager, or remove the count columns and load them into List Manager as a manual query.

    If you have a SQL expert on staff I would suggest hitting them up about this as it's pretty easy to run against the DB. There may also be an easier way to do this for you but I use SQL + Pivot Tables over T-Stats as T-Stats is a bit clunky for me.

Reply
  • $organization

    It is fairly easy if you know how to execute the SQL

    All donations, including 0 values:

    select customer_no, COUNT(*) from t_contribution where cont_dt > dateadd(YEAR, -10, GETDATE()) group by customer_no having COUNT(*)>= 7;

    All donations, not including 0 values:

    select customer_no, COUNT(*) from t_contribution where cont_amt > 0 and cont_dt > dateadd(YEAR, -10, GETDATE()) group by customer_no having COUNT(*)>= 7;

     

    Aggregate subquery, if you want to include creditees:

    select customer_no, cont_amt, cont_dt from T_CONTRIBUTION

    union all

    select creditee_no, credit_amt, cont_dt from T_CREDITEE join T_CONTRIBUTION on T_CONTRIBUTION.ref_no = T_CREDITEE.ref_no

     

    Apologies if I got anything wrong but I'm just showing these as examples. Outright I will tell you this is based on Contribution amount and not Rec'd Amount. Also you should probably remove the anon customer 0 which will most likely be included in this list.

    You can run these against your Impresario DB in SQL Server Manager, or remove the count columns and load them into List Manager as a manual query.

    If you have a SQL expert on staff I would suggest hitting them up about this as it's pretty easy to run against the DB. There may also be an easier way to do this for you but I use SQL + Pivot Tables over T-Stats as T-Stats is a bit clunky for me.

Children
No Data