Output set for last gift amount per donor

Good morning Stephanie,

We have this as an Output Set Element, as well as a few other riffs/customizations on it.

To get the most basic last gift amount information, you'll want to enter the following in TR_QUERY_ELEMENT:

• Category: Contribution
• Data Select: !.last_gift_amt
• Data From: lvs_giftstats

That will get you just the amount of whatever the last contribution amount was for a donor.

We also have additional values for the Campaign, Date, and Fund of the last gift. In those cases, you can use the same Category and Data From values, and just replace the Data Select with:

• !.last_campaign
• !.last_gift_dt
• !.last_fund

Hope this helps!

Thank you,

Brian

Thank you Brian! My eyes are going a little cross-eyed but I will have a go in the next few days and let you know how I go! 

Ok, I was excited so I have given it a go! I added the four new output set elements, then created an output set including them. It came up with a note saying that more than expected would come up, and then an error message. Do I need to do something different other than Create a new Output set, or within it?

Sorry, I mean Execute an Output Set...

Try checking the "Single Row" checkbox in TR_QUERY_ELEMENT; I glossed over that in my response earlier. That should limit to just a single value per customer ID.

Aha, will try that, thanks!

Hi Brian...tried this straight away, had trouble and only now back looking at it. when I run this I get an error message attached, basically saying that lvs_giftstats is an invalid object name...any thoughts??

Well darn! I almost wonder if lvs_giftstats is a view that needs to be installed in your database then. I did not do the initial configuration, I just benefitted from it being available to use.

Well darn! I almost wonder if lvs_giftstats is a view that needs to be installed in your database then. I did not do the initial configuration, I just benefitted from it being available to use.

Hi there .. lvs_ object means it's a local view and not standard views.  Brian, if you have access to the database via SSMS you could script out a create to file of the view to pass along for Stephanie to run.  Stephanie - you'll just need to be sure to add permissions on the view to impusers in order to use it.  

I would love to have this as well...Brian Parker (Past Member)did you have any luck pulling the file of the view?

Hey Stephanie Kemp (Past Member), ;

Sorry for being way behind on this! We've been in some beta testing lately and this fell off my desk completely.

USE [impresario]
GO

/****** Object:  View [dbo].[LVS_GIFTSTATS]    Script Date: 9/11/2018 9:32:26 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO



/*
	6/22/2015 by Matthew Echert
	View to compile the following stats for export for iWave batch screening:
		-largest gift amount
		-largest gift date
		-last gift amount
		-last gift date
		-first gift amount
		-first gift date
		
		Output set elements already exist for these two:
		-Total Gift Count (Lifetime # of Gifts)
		-Total Gift Amount (Lifetime Total Giving)
		
	8/6/15 changed to use VS_CONTRIBUTION instead of LVS_CREDIT_CONT -ME
*/

CREATE VIEW [dbo].[LVS_GIFTSTATS] AS
	WITH lgift_amt AS (
		SELECT customer_no
			, MAX(cont_amt) AS amt		--largest gift amount per cust
		FROM VS_CONTRIBUTION
		WHERE customer_no <> 0
		GROUP BY customer_no
	)

	, lgift_dt AS (
		SELECT cc.customer_no 
			, MAX(cont_dt)	dt	--date of most recent gift of largest amt
		FROM VS_CONTRIBUTION cc		
		JOIN lgift_amt la ON cc.customer_no = la.customer_no
				AND la.amt = cc.cont_amt
		GROUP BY cc.customer_no
	)

	, first_gift AS (
		SELECT customer_no
			, MIN(ref_no) AS ref_no
		FROM VS_CONTRIBUTION
		WHERE cont_amt > 0
		GROUP BY customer_no
	)

	, last_gift AS (
		SELECT customer_no
			, MAX(ref_no) AS ref_no
		FROM VS_CONTRIBUTION
		WHERE cont_amt > 0
		GROUP BY customer_no
	)

	SELECT la.customer_no
		, la.amt AS largest_gift_amt
		, ld.dt AS largest_gift_dt
		, lg_cont.cont_amt AS last_gift_amt
		, lg_cont.cont_dt AS last_gift_dt
		, cn.description AS last_campaign
		, fu.description AS last_fund
		, fg_cont.cont_amt AS first_gift_amt
		, fg_cont.cont_dt AS first_gift_dt
		, ca.description AS first_campaign
		, fd.description AS first_fund
		
	FROM lgift_amt la
	JOIN lgift_dt ld ON la.customer_no = ld.customer_no
	JOIN first_gift fg ON la.customer_no = fg.customer_no
	JOIN last_gift lg ON la.customer_no = lg.customer_no
	JOIN VS_CONTRIBUTION fg_cont ON fg.ref_no = fg_cont.ref_no
	JOIN VS_CONTRIBUTION lg_cont ON lg.ref_no = lg_cont.ref_no
	JOIN VS_CAMPAIGN ca ON ca.campaign_no = fg_cont.campaign_no
	JOIN VS_CAMPAIGN cn ON cn.campaign_no = lg_cont.campaign_no
	JOIN VS_FUND fd ON fd.fund_no = fg_cont.fund_no
	JOIN VS_FUND fu ON fu.fund_no = lg_cont.fund_no
	
	

GO

Attached please find the SQL file for LVS_GIFTSTATS that I generated thanks to Rebecca Harriman's helpful direction.

Thank you, and apologies again!

Brian

Oh my word, thank you Rebecca and Brian, very much! I will have a go...likely not for a couple weeks but I really appreciate it! I am not a coder nor do we have a coder, but I will see what i can do...might need to check in on your meaning Rebecca re permissions, when I get to it :-)

This is fantastic! Ran the script and the outputs are working like a charm! Thanks so much, Brian Parker (Past Member)!!

Ok....WHERE do you run the script (I am not a coder...can you tell?)

Hey Stephanie,

Sorry for the delayed response here! I've been hosting my parents in town for the last week, so I've been away from the forums.

If you have access to the SSMS application when you log in to RAMP, you'll be running the script there for your database; you could just open that SQL file I shared in this thread and execute it, or copy-paste the code into the window and execute.

Thank you,

Brian