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.

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

    Sample Report

The recommended name is ``AgeRangeGivingUnits``

Use the following code to Create the SQL Script.
See :doc:`../CreateSqlScript`.

.. code-block:: sql

    -- 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.