# Giving by Generational Age Bands

12.0
Administration / Finance
Intermediate

This is a variant of the Giving by Age Bands report: https://community.rockrms.com/recipes/349/report-on-giving-by-age-bands

The change is that the person's age is grouped by fixed Generational definitions. So in the Age report you'd gradually move through the old age bands over time. But in the generational report, you'll always be Gen X (or whatever)

/*

Author: Simple Donation, Josh Crews, Jeff Ward

Created Date: 07/26/2023

Description: This SQL is designed to show giving information (registration payments are excluded) of families based on the head of household.

The original intent of this was to have information around the giving of different generational segments so that teaching could be tailored as needed

to the generational groups that needed it. It returns generational bands, the # of families giving in those generational bands, the number of those families giving

over $100 per year, the percent generational of giving families who give over $100 per year, the total giving per year of that generational band, the median annual gift,

the number of new givers (in the year) in that generational band, and the number of givers who used to be in that generational band, but aren't any more.

This is like Giving by Age Bands Report https://community.rockrms.com/recipes/349/report-on-giving-by-age-bands

*/

DROP TABLE IF EXISTS #GIVERS

DROP TABLE IF EXISTS #MEDIANGIVING

SELECT PersonId, GenerationName,

CASE WHEN IsAnnualGiver = 0 AND IsPriorGiver = 1 THEN NULL ELSE ROUND(TotalAmount,0) END AS TotalAmount,

IsAnnualGiver, IsPriorGiver,

CASE WHEN IsAnnualGiver = 1 AND IsPriorGiver = 0 THEN 1 ELSE 0 END AS IsNew,

CASE WHEN IsAnnualGiver = 0 AND IsPriorGiver = 1 THEN 1 ELSE 0 END AS IsLapsed

INTO #GIVERS

FROM (

SELECT x1.PersonId, x1.[GenerationName],

SUM(CASE WHEN x2.IsRecent = 1 THEN t.Amount ELSE 0 END) AS [TotalAmount],

MAX(x2.IsRecent) AS IsAnnualGiver,

MAX(x2.IsPrior) AS IsPriorGiver

FROM [AnalyticsFactFinancialTransaction] t

CROSS APPLY (

SELECT hoh.PersonId,

CASE

WHEN dp.BirthYear < 1925 THEN '1Greatest'

WHEN dp.BirthYear BETWEEN 1925 AND 1945 THEN '2Silent'

WHEN dp.BirthYear BETWEEN 1946 AND 1964 THEN '3Boomer'

WHEN dp.BirthYear BETWEEN 1965 AND 1980 THEN '4GenX'

WHEN dp.BirthYear BETWEEN 1981 AND 1996 THEN '5Millennial'

WHEN dp.BirthYear BETWEEN 1997 AND 2012 THEN '6GenZ'

WHEN dp.BirthYear > 2012 THEN '7GenAlpha'

WHEN dp.BirthYear IS NULL THEN '8NoAge'

END AS [GenerationName]

FROM AnalyticsDimFamilyHeadOfHousehold hoh

JOIN [AnalyticsDimPersonCurrent] dp ON hoh.PersonId = dp.PersonId

WHERE hoh.GivingGroupId = t.GivingGroupId

AND dp.RecordTypeValueId = 1 --Person

AND hoh.GivingLeaderId = hoh.PersonId) x1

CROSS APPLY (

SELECT CASE WHEN t.TransactionDateTime > DATEADD(year, - 1, GETDATE()) THEN 1 ELSE 0 END AS IsRecent,

CASE WHEN t.TransactionDateTime <= DATEADD(year, - 1, GETDATE()) AND t.TransactionDateTime > DATEADD(year, - 2, GETDATE()) THEN 1 ELSE 0 END AS IsPrior) x2

WHERE t.TransactionTypeValueId = 53 --Contribution

AND t.TransactionDateTime > DATEADD(year, - 2, GETDATE())

GROUP BY x1.PersonId,

x1.GenerationName) a

SELECT DISTINCT ag.GenerationName,

PERCENTILE_CONT(0.5) WITHIN

GROUP (

ORDER BY ag.TotalAmount

) OVER (PARTITION BY ag.GenerationName) [MedianAnnualGiving]

INTO #MEDIANGIVING

FROM #GIVERS ag

WHERE IsAnnualGiver = 1;

SELECT a.GenerationName,

GivingFamiliesInBand,

GreaterThan100Families,

PercentOver100Families,

BandTotal,

FORMAT(mg.MedianAnnualGiving, 'C') AS [MedianAnnualGiving],

NewGivers,

Lapsed

FROM (

SELECT gtnl.GenerationName,

COUNT(gtnl.TotalAmount) AS [GivingFamiliesInBand],

SUM(CASE

WHEN gtnl.TotalAmount > 100

THEN 1

ELSE 0

END) AS [GreaterThan100Families],

FORMAT((

SUM(CASE

WHEN gtnl.TotalAmount > 100

THEN 1

ELSE 0

END)

) / (COUNT(gtnl.TotalAmount) * 1.00), 'P') AS [PercentOver100Families],

FORMAT(SUM(gtnl.TotalAmount), 'C') AS [BandTotal],

SUM(CASE

WHEN gtnl.IsNew = 1

THEN 1

ELSE 0

END) AS [NewGivers],

SUM(CASE

WHEN gtnl.IsLapsed = 1

THEN 1

ELSE 0

END) AS [Lapsed]

FROM #GIVERS gtnl

GROUP BY gtnl.GenerationName

) a

INNER JOIN #MEDIANGIVING mg ON a.GenerationName = mg.GenerationName

DROP TABLE IF EXISTS #GIVERS

DROP TABLE IF EXISTS #MEDIANGIVING