Age Range Giving Units

This script shows the Age Range and totals for number of giving units (households) and amounts.

You can modify the parameters at the top of the script to filter by the year/starting month, campus, fundid, member status and main fellowship indicator.

https://i.tpsdb.com/2016-03-24_09-09-28.png

Sample Report

The recommended name is AgeRangeGivingUnits

Use the following code to Create the SQL Script. See How to create a SQL Script.

-- Parameters
DECLARE
     @fiscalstartmonth INT = 1
    ,@year INT = 2015
    ,@fundid INT = 1
    ,@mintotal INT = 100
    ,@memberstatusid INT = NULL
    ,@inmainfellowship BIT = NULL
    ,@campusid INT = 0

DECLARE @fromdt DATETIME = DATETIMEFROMPARTS(@year, @fiscalstartmonth, 1, 0, 0, 0, 0)
DECLARE @todt DATETIME = DATEADD(DAY, -1, DATEADD(YEAR, 1, @fromdt))

-- Constants
DECLARE
     @nontaxded BIT = 0
    ,@pledges BIT = 0
    ,@includeUnclosed BIT = 1

;WITH gifts AS (
    SELECT
        c2.FamilyId,
        c2.FundId,
        [Count] = COUNT(*),
        Amount = SUM(c2.Amount),
        PledgeAmount = SUM(c2.PledgeAmount)
    FROM dbo.Contributions2(@fromdt, @todt, @campusid, @pledges, @nontaxded, @includeUnclosed, NULL) c2
    GROUP BY c2.FamilyId, c2.FundId
),
units AS (
    SELECT
        hh.FamilyId,
        Age = ISNULL(hh.Age, -1),
        [Count] = COUNT(*),
        Amount = SUM(tt.Amount)
    FROM gifts tt
    JOIN dbo.Families f ON f.FamilyId = tt.FamilyId
    JOIN dbo.People hh ON hh.PeopleId = f.HeadOfHouseholdId
    LEFT OUTER JOIN dbo.Organizations o ON o.OrganizationId = hh.BibleFellowshipClassId
    WHERE (@fundid IS NULL OR tt.FundId = @fundid)
    AND (@memberstatusid IS NULL OR @memberstatusid = hh.MemberStatusId)
    AND (@inmainfellowship IS NULL OR @inmainfellowship = IIF(hh.BibleFellowshipClassId > 0, 1, 0))
    GROUP BY hh.FamilyId, hh.Age
    HAVING SUM(Amount) > @mintotal
),
ageranges AS (
    SELECT
        AgeRange = CASE
            WHEN units.Age < 0 THEN 'No age'
            WHEN units.Age <= 10 THEN '10'
            WHEN units.Age <= 20 THEN '20'
            WHEN units.Age <= 30 THEN '30'
            WHEN units.Age <= 40 THEN '40'
            WHEN units.Age <= 50 THEN '50'
            WHEN units.Age <= 60 THEN '60'
            WHEN units.Age <= 70 THEN '70'
            WHEN units.Age <= 80 THEN '80'
            WHEN units.Age <= 99 THEN '> 80'
            ELSE '> 100'
        END,
        units.Age,
        units.[Count],
        units.Amount
    FROM units
)

SELECT
    AgeRange = ISNULL(r.AgeRange, 'Total'),
    [Units] = SUM(r.Count),
    [Total] = SUM(r.Amount)
FROM ageranges r
GROUP BY r.AgeRange WITH ROLLUP
ORDER BY
    CASE WHEN r.AgeRange LIKE 'No%' THEN 0
    WHEN r.AgeRange LIKE '> 8%' THEN 2
    WHEN r.AgeRange LIKE '> 1%' THEN 3
    WHEN r.AgeRange IS NULL THEN 4
    ELSE 1
    END, r.AgeRange


Latest Update

11/13/2020

Modify image link with secure protocol.