How to translate this SQL query with Raiser's Edge Database view?

Kim Tsang
Kim Tsang
Name Dropper Facilitator 1 September 2024 Monthly Challenge
in Blackbaud Raiser's Edge NXT®: General Discussions

Hello,

I am fairly new to RE NXT. I would like to run a query like this:

(Select first_name, last_name, gift_date, sum(gift_amount)

From giftdata

Where location = “San Francisco”

Group By first_name, last_name, gift_date

Having sum(gift_amount) > 1000;)

Can I run a query like this in the database view of RENXT? Web view? Any help or advice would be greatly appreciated.

Tagged:

Comments

  • Alex Wong
    Alex Wong Community All-Star
    Ninth Anniversary Kudos 5 Facilitator 3 bbcon 2025 Attendee Badge

    @Kim Tsang
    sound like you are trying to get donor who give more than $1,000 in a single day, meaning if a donor give more than 1 day over $1,000, you want multiple rows of data for the same donor, each day over $1,000 to be in each row and the amount given on each of those days.

    first namelast namegift_datetotal_given
    johnsmith1/1/2024$1,100
    johnsmith2/15/2024$2,000

    I'm not sure what purpose you are trying to get doing a report like this, but there is no way to do this in RE NXT dbview nor webview.

    one way to do this outside of RE NXT is to run a gift query of gift made by location=san francisco donor, outputing gift info, then pivot in excel or use SUMIFS formula.

  • Ashley Gelderblom
    Ashley Gelderblom
    Tenth Anniversary Kudos 2 Name Dropper Participant

    @Kim Tsang
    you can do a DB gift query on location, ensure you have all columns output, (remember to include Gift type in your export so you can filter out non income types (Recurring gift/ Pedges etc)export to excel then do a pivot table as required, sort descending by sum(amount) and you'll have your donors>1k at the top

  • Dariel Dixon
    Dariel Dixon Community All-Star
    Seventh Anniversary Kudos 5 First Reply PowerUp Challenge #3 Gift Management

    I don't think this is what the query is asking @Alex Wong. I think what @Kim Tsang is looking for is donors who live in San Francisco who have made cumulative gifts over $1,000. This can be done easily with summary information.

    I would create this query using a criteria of Preferred Address City = San Francisco AND Total Amount of Gifts ≤ $1000. You would then be able to set your output and sort information there.

  • Alex Wong
    Alex Wong Community All-Star
    Ninth Anniversary Kudos 5 Facilitator 3 bbcon 2025 Attendee Badge

    @Dariel Dixon
    Only Kim will be able to response what he is asking for. I am only speaking to the SQL he posted.

    Group By first_name, last_name, gift_date

    Having sum(gift_amount) > 1000;

    This SQL is asking for donor on any specific gift date to have sum of gift amount greater than 1000, which is the table I posted early.

  • Kim Tsang
    Kim Tsang
    Name Dropper Facilitator 1 September 2024 Monthly Challenge

    @Alex Wong @Dariel Dixon @Ashley Gelderblom Thank you guys so much for the responses. Lets say I take the date component out of the equation and would just like to see the total gifts a person has given over time in San Francisco, is there a way to do that in RE NXT? I'm just so used to using 'group by" in SQL and do not know how to do the same with RENXT ?. Thank you guys again.

  • Alex Wong
    Alex Wong Community All-Star
    Ninth Anniversary Kudos 5 Facilitator 3 bbcon 2025 Attendee Badge

    @Kim Tsang
    DB view > Constituent Query:

    • Criteria
      • location (preferred address's city = San Francisco and state = CA)
      • Summary of Gift Amount greater than 1000
        • when selected, you get to select “sub criteria”
        • gift date = date range you want to work on (no date = lifetime giving)
        • gift type = select type that make sense (pledge and pay-cash should not be selected at the same time as that will double count)
    540cbe272f99b36642c563edf0255ff6-huge-im
    • output
      • select what info youwant, then at the top where it say “Query Fields” you can select the total amount of gift
    b9d04f16ff2ef50934c84e48f5648dc2-huge-im
  • Kim Tsang
    Kim Tsang
    Name Dropper Facilitator 1 September 2024 Monthly Challenge

    @Alex Wong
    you are the MAN! Thank you very much!

  • Joe Moretti
    Joe Moretti Community All-Star
    Kudos 5 Second Anniversary Raiser's Edge NXT Fall 2025 Product Update Briefing Badge First Reply

    @Kim Tsang I would say to make sure you use “soft Credits” in the Tools/Query Options/Gift Processing using Credit Soft Credit to both, especially since many people pay thru a Donor Advisor Fund, otherwise you will get the Donor Advisor Fund and not the soft credit recipient. If you code your Donor Advised Fund records a certain way, you can then exclude those DAVF while keeping the soft credits.

Categories