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