Age Range Giving Units¶

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