I've a field with a date of birth in it.
Displayed as YYYY-MM-DD
Ex: 1990-08-19
I would like to use CASE, WHEN, THEN, END AS for this.
CASE Person.dob
WHEN <= DATEADD(YEAR, -26, GETDATE()) AND >= DATEADD(YEAR, -35, GETDATE()) THEN 'Age 26 to 35'
WHEN <= DATEADD(YEAR, -36, GETDATE()) AND >= DATEADD(YEAR, -45, GETDATE()) THEN 'Age 36 to 45'
WHEN <= DATEADD(YEAR, -46, GETDATE()) AND >= DATEADD(YEAR, -55, GETDATE()) THEN 'Age 46 to 55'
WHEN <= DATEADD(YEAR, -56, GETDATE()) AND >= DATEADD(YEAR, -65, GETDATE()) THEN 'Age 56 to 65'
WHEN <= DATEADD(YEAR, -66, GETDATE()) THEN 'Age 66+'
END AS 'Age'
When executed, the column shows something like the following
Age
Age 36 to 45
Age 26 to 35
Age 36 to 45
Age 66+
Age 66+
Age 56 to 65
Much simpler to just compute the actual age and then convert it to a range. I'll use a CROSS APPLY to calc the age to assign an alias name to the result.
SELECT ...,
CASE WHEN Person_Age >= 66 THEN 'Age 66+'
WHEN Person_Age >= 56 THEN 'Age 56 to 65'
WHEN Person_Age >= 46 THEN 'Age 46 to 55'
WHEN Person_Age >= 36 THEN 'Age 36 to 45'
WHEN Person_Age >= 26 THEN 'Age 26 to 35'
WHEN Person_Age >= 18 THEN 'Age 18 to 25'
ELSE 'Age <18'
END AS 'Age'
FROM Person
CROSS APPLY (
SELECT DATEDIFF(YEAR, Person.dob, GETDATE()) -
CASE WHEN CONVERT(char(5), Person.dob, 1) <
CONVERT(char(5), GETDATE(), 1) THEN 1 ELSE 0
END AS [Person_Age]
) AS ca1