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