# Check Whether 2nd Birthday Occurred During 2017

• ### Question

• I need to determine the patient population of individuals who turned two during the 2017 year.

I have a list of patient IDs, and their birth dates.  The results could simply be a 1 or 0 in a 3rd column indicating whether the individual turned two during the 2017 year.  Here is a small sample of the data.

PatientEpicID PatientBirthDate
100Z09101 1974-01-06
100Z20741 2015-10-13
100Z24081 1995-03-25
100Z25551 2016-06-22
100Z27301 1960-08-22

Thank you.

Thursday, November 30, 2017 5:55 PM

• where YEAR(PatientBirthDate) = 2015

Or am I missing something?

• Marked as answer by Thursday, November 30, 2017 8:17 PM
Thursday, November 30, 2017 7:23 PM
• Great, I also had to include 'Where CurrentAge = 2' Since your logic was including 1 yr old's that birthday has not past yet in 2017.

This seems to do the trick.  If current age wasn't in my datamart I would have needed to include date logic to exclude those <2.

Thank you.

• Marked as answer by Thursday, November 30, 2017 8:17 PM
Thursday, November 30, 2017 8:17 PM

### All replies

• where YEAR(PatientBirthDate) = 2015

Or am I missing something?

• Marked as answer by Thursday, November 30, 2017 8:17 PM
Thursday, November 30, 2017 7:23 PM
• ```CREATE TABLE Patient(
PatientEpicID VARCHAR(10),
PatientBirthDate DATETIME
)

INSERT INTO Patient VALUES
('100Z09101','1974-01-06'),
('100Z20741','2015-10-13'),
('100Z24081','1995-03-25'),
('100Z25551','2016-06-22'),
('100Z27301','1960-08-22')

SELECT PatientEpicID,PatientBirthDate,
CASE WHEN DATEDIFF(YEAR,PatientBirthDate,GETDATE())=2	THEN 1 ELSE 0 END 'Yes/No'
FROM Patient```

Thursday, November 30, 2017 7:46 PM
• ```declare @patienthistory TABLE(PatientEpicID varchar(100), PatientBirthDate date,indicator char(1))

insert into @patienthistory (PatientEpicID,PatientBirthDate) values ('100Z09101', '1974-01-06'),('100Z20741', '2015-10-13'),('100Z24081', '1995-03-25'),('100Z25551', '2016-06-22'),('100Z27301', '1960-08-22')

update @patienthistory
set indicator=case when 2017- year(PatientBirthDate)=2 then 'Y' else 'N' end

select * from @patienthistory```

Aparna

Thursday, November 30, 2017 8:08 PM
• Great, I also had to include 'Where CurrentAge = 2' Since your logic was including 1 yr old's that birthday has not past yet in 2017.

This seems to do the trick.  If current age wasn't in my datamart I would have needed to include date logic to exclude those <2.

Thank you.

• Marked as answer by Thursday, November 30, 2017 8:17 PM
Thursday, November 30, 2017 8:17 PM
• Great, I also had to include 'Where CurrentAge = 2' Since your logic was including 1 yr old's that birthday has not past yet in 2017.

This seems to do the trick.  If current age wasn't in my datamart I would have needed to include date logic to exclude those <2.

Thank you.

They still would have turned two in the year 2017.

But otherwise, yes, to exclude them, having the age would make it easier

Thursday, November 30, 2017 8:25 PM
• Just throw something else in here, what method of computing age are you using? Asians count the year that you're in, so everyone is always at least one-year-old. Westerners count the years you have completed. Obviously, the worst possible thing would be to have a "patient_age" column, since it would be changing constantly. I've gotten caught on that one :(

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Thursday, November 30, 2017 9:37 PM