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

Kim Tsang

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.

Alex Wong

@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 name	last name	gift_date	total_given
john	smith	1/1/2024	$1,100
john	smith	2/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 2

@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 2

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

@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

@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

@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)

• output
  • select what info youwant, then at the top where it say “Query Fields” you can select the total amount of gift

Kim Tsang

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

Joe Moretti 3

@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.