September 29, 2016 at 1:53 pm
Morning folks,
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, -13, GETDATE()) THEN 'Age less than 14'
WHEN <= DATEADD(YEAR, -14, GETDATE()) AND >= DATEADD(YEAR, -15, GETDATE()) THEN 'Age 14 to 15'
WHEN <= DATEADD(YEAR, -16, GETDATE()) AND >= DATEADD(YEAR, -17, GETDATE()) THEN 'Age 16 to 17'
WHEN <= DATEADD(YEAR, -18, GETDATE()) THEN 'Age 18'
WHEN <= DATEADD(YEAR, -19, GETDATE()) AND >= DATEADD(YEAR, -20, GETDATE()) THEN 'Age 19 to 20'
WHEN <= DATEADD(YEAR, -21, GETDATE()) AND >= DATEADD(YEAR, -25, GETDATE()) THEN 'Age 21 to 25'
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 should show 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
The age range must be accurate as of the date this gets runs.
Where have I gone awry as my code above is not working?
Thank you
September 29, 2016 at 2:15 pm
Here's a slightly different version:
DECLARE @DOB DATE = '20000101';
DECLARE @AgeYears TINYINT = DATEDIFF(YEAR, @DOB, GETDATE());
SELECT Age = (CASE WHEN @AgeYears <= 13 THEN 'Age less than 14'
WHEN @AgeYears BETWEEN 14 AND 15 THEN 'Age 14 to 15'
WHEN @AgeYears BETWEEN 16 AND 17 THEN 'Age 16 to 17'
WHEN @AgeYears = 18 THEN 'Age 18'
WHEN @AgeYears BETWEEN 19 AND 20 THEN 'Age 19 to 20'
WHEN @AgeYears BETWEEN 21 AND 25 THEN 'Age 21 to 25'
WHEN @AgeYears BETWEEN 26 AND 35 THEN 'Age 26 to 35'
WHEN @AgeYears BETWEEN 36 AND 45 THEN 'Age 36 to 45'
WHEN @AgeYears BETWEEN 46 AND 55 THEN 'Age 46 to 55'
WHEN @AgeYears BETWEEN 56 AND 65 THEN 'Age 56 to 65'
WHEN @AgeYears >= 66 THEN 'Age 66+'
END);
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 29, 2016 at 2:48 pm
Case expressions are short-circuit evaluated. The first expression that meets the criteria will be used.
This one assumes that birth dates in the future and NULL birth dates should return a NULL age. Your business requirements may vary in regard to missing or invalid data.
CREATE TABLE test (dob DATE);
-- add more values here for a complete test
INSERT TEST VALUES
('2002-09-28'),
('2002-09-30'),
('2002-09-29'),
('2000-01-01'),
('1995-01-01'),
('1990-01-01'),
(NULL),
-- date in the future
('2020-01-01');;
SELECT dob,
CASE
WHEN dob <= DATEADD(YEAR, -66, GETDATE()) THEN 'Age 66+'
WHEN dob <= DATEADD(YEAR, -56, GETDATE()) THEN 'Age 56 to 65'
WHEN dob <= DATEADD(YEAR, -46, GETDATE()) THEN 'Age 46 to 55'
WHEN dob <= DATEADD(YEAR, -36, GETDATE()) THEN 'Age 36 to 45'
WHEN dob <= DATEADD(YEAR, -26, GETDATE()) THEN 'Age 26 to 35'
WHEN dob <= DATEADD(YEAR, -21, GETDATE()) THEN 'Age 21 to 25'
WHEN dob <= DATEADD(YEAR, -19, GETDATE()) THEN 'Age 19 to 20'
WHEN dob <= DATEADD(YEAR, -18, GETDATE()) THEN 'Age 18'
WHEN dob <= DATEADD(YEAR, -16, GETDATE()) THEN 'Age 16 to 17'
WHEN dob <= DATEADD(YEAR, -14, GETDATE()) THEN 'Age 14 to 15'
WHEN dob <= GETDATE() THEN 'Age less than 14'
END AS Age
from test
order by dob;
September 29, 2016 at 3:42 pm
There are two variants of the CASE expression: the simple CASE expression and the searched CASE expression. You are mixing the two.
When using the simple CASE expression, the comparison is always "equal". Since you need to use ranges, you cannot use the simple CASE expression.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply