Report of member's ages: this percentage is young adult or median adult or children, etc.

HI, does anyone already know of a report or list that will help me see what percentage of our membership fall into certain age categories? What percentage are children? What percentage are senior adults, etc.?

Parents
No Data
Reply
  • Here is a query that you can easily turn in to a stored procedure and call from a report: 

    SELECT
    NumValidMemberBirthdates
    ,BirthDatesInDateRanges
    ,[1825]
    ,CAST(CAST([1825] AS DECIMAL(18,2)) / BirthDatesInDateRanges * 100 AS DECIMAL(18,2)) AS [1825%]
    ,[2635]
    ,CAST(CAST([2635] AS DECIMAL(18,2)) / BirthDatesInDateRanges * 100 AS DECIMAL(18,2)) AS [2635%]
    ,[3645]
    ,CAST(CAST([3645] AS DECIMAL(18,2)) / BirthDatesInDateRanges * 100 AS DECIMAL(18,2)) AS [3645%]
    ,[4655]
    ,CAST(CAST([4655] AS DECIMAL(18,2)) / BirthDatesInDateRanges * 100 AS DECIMAL(18,2)) AS [4655%]
    ,[56+]
    ,CAST(CAST([56+] AS DECIMAL(18,2)) / BirthDatesInDateRanges * 100 AS DECIMAL(18,2)) AS [56+%]
    FROM
    (SELECT
    MIN(NumValidMemberBirthdates) AS NumValidMemberBirthdates
    ,SUM(CASE WHEN DATEDIFF(hour, birth_date, CURRENT_TIMESTAMP) / 8766 > 17 THEN 1 ELSE 0 END) AS BirthDatesInDateRanges
    ,SUM(CASE WHEN DATEDIFF(hour, birth_date, CURRENT_TIMESTAMP) / 8766 > 17
    AND DATEDIFF(hour, birth_date, CURRENT_TIMESTAMP) / 8766 < 26 THEN 1 ELSE 0 END) AS [1825]
    ,SUM(CASE WHEN DATEDIFF(hour, birth_date, CURRENT_TIMESTAMP) / 8766 > 25
    AND DATEDIFF(hour, birth_date, CURRENT_TIMESTAMP) / 8766 < 36 THEN 1 ELSE 0 END) AS [2635]
    ,SUM(CASE WHEN DATEDIFF(hour, birth_date, CURRENT_TIMESTAMP) / 8766 > 35
    AND DATEDIFF(hour, birth_date, CURRENT_TIMESTAMP) / 8766 < 46 THEN 1 ELSE 0 END) AS [3645]
    ,SUM(CASE WHEN DATEDIFF(hour, birth_date, CURRENT_TIMESTAMP) / 8766 > 45
    AND DATEDIFF(hour, birth_date, CURRENT_TIMESTAMP) / 8766 < 56 THEN 1 ELSE 0 END) AS [4655]
    ,SUM(CASE WHEN DATEDIFF(hour, birth_date, CURRENT_TIMESTAMP) / 8766 > 55 THEN 1 ELSE 0 END) AS [56+]
    FROM (SELECT
    COUNT(*) OVER (PARTITION BY 1) AS NumValidMemberBirthdates
    ,birth_date
    FROM core_person
    WHERE record_status = 0 AND ISDATE(birth_date) = 1 AND birth_date <> '1/1/1900'
    ) AS validbirthdates
    ) AS range_counts
     

    It is important to note when designing your queries to design them in a way to read the table one time. Repeatedly reading a table in the same query can lead to performance issues with more complex queries. 

Children
No Data
More Content