This query pulls counts for current-ish patron accounts that are unexpired or expired within the last three years, with the output delivering age groups. While you’ll likely need to define your age groups differently, this could serve as a template for a similar query, or as a basis for another query by age groups.
-- Creatre a table to hold the totals
CREATE TABLE #TempPatronsServedAge (
Age0to10 INT,
Age11to18 INT,
Age19to65 INT,
Age65 INT,
Total INT
);
-- Populate that table
INSERT INTO #TempPatronsServedAge
SELECT
COUNT(CASE WHEN pr.Birthdate BETWEEN '20131020' AND '20231020' THEN 1 END),
COUNT(CASE WHEN pr.Birthdate BETWEEN '20051020' AND '20131019' THEN 1 END),
COUNT(CASE WHEN pr.Birthdate BETWEEN '19581020' AND '20051019' THEN 1 END),
COUNT(CASE WHEN pr.Birthdate BETWEEN '19131020' AND '19581019' THEN 1 END),
0
FROM
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK)
INNER JOIN
Polaris.Polaris.Patrons p WITH (NOLOCK)
ON (p.PatronID = pr.PatronID)
INNER JOIN
Polaris.Polaris.Organizations o WITH (NOLOCK)
ON (o.OrganizationID = p.OrganizationID)
WHERE -- Adjust expiration dates as needed
((pr.ExpirationDate >= DATEADD(year,-3,GETDATE())) OR (pr.ExpirationDate is NULL))
AND -- Record status is Final
p.RecordStatusID = 1
AND -- Limit organizations if you like
p.OrganizationID IN (
SELECT OrganizationID
FROM Polaris.Polaris.Organizations WITH (NOLOCK)
WHERE ParentOrganizationID = 9
);
-- Update our table to clear the 0 totals (as needed) and replace with real data
UPDATE #TempPatronsServedAge
SET Total = (SELECT SUM(Age0to10 + Age11to18 + Age19to65 + Age65) FROM #TempPatronsServedAge);
-- Final delivery
SELECT
FORMAT(Age0to10, '#,0') AS [Age 0 - 10],
FORMAT(Age11to18, '#,0') AS [Age 11 - 18],
FORMAT(Age19to65, '#,0') AS [Age 19 - 65],
FORMAT(Age65, '#,0') AS [Age 65+],
FORMAT(Total, '#,0') AS [Total]
FROM
#TempPatronsServedAge;
-- Tidy up
DROP TABLE #TempPatronsServedAge;