Tuesday, April 04, 2006

Age calculation

Question:
From tblAge (fields: DOB, City),
I need to pull out the counts of ages specified (let say Age_Category) below:

50 & under
51-64
65-69
70-74
75-79
80-84
85 & up

The output is to show:

Age_Category, City, count (city) based on DOB vs the date specified (let say 10/30/05).

Gabbar:
SELECT
CASE
WHEN DATEDIFF(YEAR, DOB, getdate()) < 51 THEN '50 & under'
WHEN DATEDIFF(YEAR, DOB, getdate()) BETWEEN 51 AND 64 THEN '51-64'
-- add another betweens cases here
WHEN DATEDIFF(YEAR, DOB, getdate()) > 84 THEN '85 & up'
END
AS Age_Category,
City,
count(city)
FROM
tblAge
GROUP BY
City,
CASE
WHEN DATEDIFF(YEAR, DOB, getdate()) < 51 THEN '50 & under'
WHEN DATEDIFF(YEAR, DOB, getdate()) BETWEEN 51 AND 64 THEN '51-64'
-- add another betweens cases here
WHEN DATEDIFF(YEAR, DOB, getdate()) > 84 THEN '85 & up'
END

No comments: