Last Gift Amount and Max Gift Date

Hi Kelly

Just a couple of days ago I was reworking the First and Last Gift Elements that are in the Cookbook and I am still testing out the views. I think this might be what you are after for the Last gift and the corresponding date.

Give the View below a go. There is a bit that is commented out about desig_codes which are Designation Codes attached to the Fund not the Contribution. Some sites use these to identify Funds that are strictly Gifts vs Memberships, Elevated Events and so forth. If you don’t care what the gift amount was for then keep this the way it is.

Let me know if it works for you as that will speed up my testing process and an updated version of the Cookbook will get uploaded to the website.

Cheers

Sandra

USE [impresario]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/* For Output Set Builder Element, finds last gift regardless of Campaign or Role. Campaign fyear and cont_dt are used to determine the last gift.

Extra WHERE clause for desig_code can be uncommented and enter your desig_code/s if you wish to limit it to specific funds considered to be true contributions */

CREATE VIEW [dbo].[LVS_CONTRIBUTION_LASTGIFT]

AS

SELECT *

FROM (

     SELECT co.customer_no

,co.role

,co.cont_amt

,co.recd_amt

,co.fund_no

,fund_desc = b.description

,co.cont_dt

,co.campaign_no

,campaign_name = ca.description

,ca.fyear

,s.source_name

,pos = ROW_NUMBER() OVER(PARTITION BY customer_no ORDER BY cont_dt DESC, fyear DESC)

     FROM vs_contribution_with_initiator co

 join VS_CAMPAIGN ca

on co.campaign_no = ca.campaign_no

JOIN TX_APPEAL_MEDIA_TYPE s (NOLOCK) on s.source_no = co.source_no

JOIN VS_FUND b on co.fund_no = b.fund_no

where co.cont_amt > 0

-- and b.desig_code in (##,##)

-- REPLACE ## with the desig_code ids that represent funds your Org considers Contributions

   ) a

WHERE a.pos = 1     

GO

GRANT REFERENCES, SELECT on [LVS_CONTRIBUTION_LASTGIFT] to impusers

GO

From: Tessitura Development Forum [mailto:forums-development@tessituranetwork.com] On Behalf Of Kelly Enderwick
Sent: Wednesday, April 27, 2016 2:23 AM
To: Sandra Ashby <sashby@tessituranetwork.com>
Subject: Re: [Tessitura Development Forum] Last Gift Amount and Max Gift Date

I've had a look in the Cookbook and they didn't seem quite what I was after. 

There is one that looks at last cont to a specific Campaign - I'll have a look at amending that, but I was sure someone would have done that already. Being a little lazy. 

Kelly

Hi Kelly

Just a couple of days ago I was reworking the First and Last Gift Elements that are in the Cookbook and I am still testing out the views. I think this might be what you are after for the Last gift and the corresponding date.

Give the View below a go. There is a bit that is commented out about desig_codes which are Designation Codes attached to the Fund not the Contribution. Some sites use these to identify Funds that are strictly Gifts vs Memberships, Elevated Events and so forth. If you don’t care what the gift amount was for then keep this the way it is.

Let me know if it works for you as that will speed up my testing process and an updated version of the Cookbook will get uploaded to the website.

Cheers

Sandra

USE [impresario]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/* For Output Set Builder Element, finds last gift regardless of Campaign or Role. Campaign fyear and cont_dt are used to determine the last gift.

Extra WHERE clause for desig_code can be uncommented and enter your desig_code/s if you wish to limit it to specific funds considered to be true contributions */

CREATE VIEW [dbo].[LVS_CONTRIBUTION_LASTGIFT]

AS

SELECT *

FROM (

     SELECT co.customer_no

,co.role

,co.cont_amt

,co.recd_amt

,co.fund_no

,fund_desc = b.description

,co.cont_dt

,co.campaign_no

,campaign_name = ca.description

,ca.fyear

,s.source_name

,pos = ROW_NUMBER() OVER(PARTITION BY customer_no ORDER BY cont_dt DESC, fyear DESC)

     FROM vs_contribution_with_initiator co

 join VS_CAMPAIGN ca

on co.campaign_no = ca.campaign_no

JOIN TX_APPEAL_MEDIA_TYPE s (NOLOCK) on s.source_no = co.source_no

JOIN VS_FUND b on co.fund_no = b.fund_no

where co.cont_amt > 0

-- and b.desig_code in (##,##)

-- REPLACE ## with the desig_code ids that represent funds your Org considers Contributions

   ) a

WHERE a.pos = 1     

GO

GRANT REFERENCES, SELECT on [LVS_CONTRIBUTION_LASTGIFT] to impusers

GO

From: Tessitura Development Forum [mailto:forums-development@tessituranetwork.com] On Behalf Of Kelly Enderwick
Sent: Wednesday, April 27, 2016 2:23 AM
To: Sandra Ashby <sashby@tessituranetwork.com>
Subject: Re: [Tessitura Development Forum] Last Gift Amount and Max Gift Date

I've had a look in the Cookbook and they didn't seem quite what I was after. 

There is one that looks at last cont to a specific Campaign - I'll have a look at amending that, but I was sure someone would have done that already. Being a little lazy. 

Kelly