Find patients ages 13 - 21 as of calendar year

Question

Find patients ages 13 - 21 as of calendar year

NineIron

SSChampion

Points: 12537
More actions
November 9, 2017 at 8:59 am

#333524

I need to find patients who will be between the ages of 12 and 21 within a given calendar year. I've included some sample data.
Thanx.
create table #T
(
PatientID int,
DOB datetime
)
insert into #T(PatientID, DOB) values(1,'04/05/1999')
insert into #T(PatientID, DOB) values(2,'09/11/1998')
insert into #T(PatientID, DOB) values(3,'10/09/1996')
insert into #T(PatientID, DOB) values(4,'10/20/1999')
insert into #T(PatientID, DOB) values(5,'09/10/2000')
insert into #T(PatientID, DOB) values(6,'03/30/1999')
insert into #T(PatientID, DOB) values(7,'06/02/2001')
insert into #T(PatientID, DOB) values(8,'12/08/1994')
insert into #T(PatientID, DOB) values(9,'01/25/1998')
insert into #T(PatientID, DOB) values(10,'11/15/1996')
insert into #T(PatientID, DOB) values(11,'12/13/1997')
insert into #T(PatientID, DOB) values(12,'01/06/2004')
insert into #T(PatientID, DOB) values(13,'10/29/1995')
insert into #T(PatientID, DOB) values(14,'08/12/1996')
insert into #T(PatientID, DOB) values(15,'05/05/1997')
insert into #T(PatientID, DOB) values(16,'11/01/1996')
insert into #T(PatientID, DOB) values(17,'08/21/1992')
insert into #T(PatientID, DOB) values(18,'12/11/1999')
insert into #T(PatientID, DOB) values(19,'10/02/1995')
insert into #T(PatientID, DOB) values(20,'06/22/2004')
insert into #T(PatientID, DOB) values(21,'01/29/1998')
insert into #T(PatientID, DOB) values(22,'09/02/2002')
insert into #T(PatientID, DOB) values(23,'04/21/1997')
insert into #T(PatientID, DOB) values(24,'02/05/1992')
insert into #T(PatientID, DOB) values(25,'03/21/1996')
insert into #T(PatientID, DOB) values(26,'07/22/1996')
insert into #T(PatientID, DOB) values(27,'08/24/1994')
insert into #T(PatientID, DOB) values(28,'11/17/1992')
insert into #T(PatientID, DOB) values(29,'05/17/1995')
insert into #T(PatientID, DOB) values(30,'08/31/2000')
insert into #T(PatientID, DOB) values(31,'08/31/2000')
insert into #T(PatientID, DOB) values(32,'10/07/1999')
insert into #T(PatientID, DOB) values(33,'11/30/1999')
insert into #T(PatientID, DOB) values(34,'10/23/1997')
insert into #T(PatientID, DOB) values(35,'03/22/1999')
insert into #T(PatientID, DOB) values(36,'01/21/2003')
insert into #T(PatientID, DOB) values(37,'01/18/1998')
insert into #T(PatientID, DOB) values(38,'09/06/1997')
insert into #T(PatientID, DOB) values(39,'05/27/2001')
insert into #T(PatientID, DOB) values(40,'10/11/1991')
insert into #T(PatientID, DOB) values(41,'08/19/1990')
insert into #T(PatientID, DOB) values(42,'05/24/1989')
insert into #T(PatientID, DOB) values(43,'05/22/1997')
insert into #T(PatientID, DOB) values(44,'06/21/2002')
insert into #T(PatientID, DOB) values(45,'06/14/1995')
insert into #T(PatientID, DOB) values(46,'11/19/1999')
insert into #T(PatientID, DOB) values(47,'09/23/1996')
insert into #T(PatientID, DOB) values(48,'01/04/1999')
insert into #T(PatientID, DOB) values(49,'01/15/1995')
insert into #T(PatientID, DOB) values(50,'10/26/1993')
insert into #T(PatientID, DOB) values(51,'05/20/2002')
insert into #T(PatientID, DOB) values(52,'05/26/1989')
insert into #T(PatientID, DOB) values(53,'05/03/1999')
insert into #T(PatientID, DOB) values(54,'03/09/1996')
insert into #T(PatientID, DOB) values(55,'11/17/1995')
insert into #T(PatientID, DOB) values(56,'03/30/1996')
insert into #T(PatientID, DOB) values(57,'11/06/1998')
insert into #T(PatientID, DOB) values(58,'02/16/2001')
insert into #T(PatientID, DOB) values(59,'02/03/1997')
insert into #T(PatientID, DOB) values(60,'07/24/1999')
insert into #T(PatientID, DOB) values(61,'04/18/2003')
insert into #T(PatientID, DOB) values(62,'05/27/1995')
insert into #T(PatientID, DOB) values(63,'01/26/1998')
insert into #T(PatientID, DOB) values(64,'09/06/1999')
insert into #T(PatientID, DOB) values(65,'07/18/1999')
insert into #T(PatientID, DOB) values(66,'04/15/1994')
insert into #T(PatientID, DOB) values(67,'02/09/1997')
insert into #T(PatientID, DOB) values(68,'12/30/1995')
insert into #T(PatientID, DOB) values(69,'02/04/1997')
insert into #T(PatientID, DOB) values(70,'08/15/1994')
insert into #T(PatientID, DOB) values(71,'06/19/1993')
insert into #T(PatientID, DOB) values(72,'02/16/1995')
insert into #T(PatientID, DOB) values(73,'01/17/1996')
insert into #T(PatientID, DOB) values(74,'10/25/1995')
insert into #T(PatientID, DOB) values(75,'09/27/1997')
insert into #T(PatientID, DOB) values(76,'06/11/1997')
insert into #T(PatientID, DOB) values(77,'11/04/1993')
insert into #T(PatientID, DOB) values(78,'07/12/2005')
insert into #T(PatientID, DOB) values(79,'01/05/1998')
insert into #T(PatientID, DOB) values(80,'08/17/1996')
insert into #T(PatientID, DOB) values(81,'04/27/1996')
insert into #T(PatientID, DOB) values(82,'09/15/1996')
insert into #T(PatientID, DOB) values(83,'05/18/2003')
insert into #T(PatientID, DOB) values(84,'10/27/1995')
insert into #T(PatientID, DOB) values(85,'08/09/1995')
insert into #T(PatientID, DOB) values(86,'12/01/1996')
insert into #T(PatientID, DOB) values(87,'11/09/1997')
insert into #T(PatientID, DOB) values(88,'02/22/1999')
insert into #T(PatientID, DOB) values(89,'10/23/1991')
insert into #T(PatientID, DOB) values(90,'01/15/1996')
insert into #T(PatientID, DOB) values(91,'06/05/1997')
insert into #T(PatientID, DOB) values(92,'05/27/1995')
insert into #T(PatientID, DOB) values(93,'12/16/1995')
insert into #T(PatientID, DOB) values(94,'08/06/1997')
insert into #T(PatientID, DOB) values(95,'08/29/2000')
insert into #T(PatientID, DOB) values(96,'12/11/1999')
insert into #T(PatientID, DOB) values(97,'10/04/2002')
insert into #T(PatientID, DOB) values(98,'05/27/2004')
insert into #T(PatientID, DOB) values(99,'10/26/1992')

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99134 More actions · Answer 1

Your logic is a bit vague, you say between ages 13 and 21 in your subject, and 12-21 in your post. Also, when should they count? For example, if someone turns 12/13 on 31 December do you include them for that yaer? What about if they turn 22 on 02 January?

This should get you you started anyway:
DECLARE @Year int = 2017;

SELECT *, DATEDIFF(YEAR, DOB, CONVERT(date, CONVERT(varchar(4),@Year))) AS Age FROM #T WHERE DATEDIFF(YEAR, DOB, CONVERT(date, CONVERT(varchar(4),@Year))) BETWEEN 13 AND 21;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

NineIron SSChampion Points: 12537 More actions · Answer 2

My bad. 12-21. So, if they are 12 on 1/1 then, include them. If they turn 22 on 12/31, exclude them.

Thom A SSC Guru Points: 99134 More actions · Answer 3

NineIron - Thursday, November 9, 2017 9:16 AM
My bad. 12-21. So, if they are 12 on 1/1 then, include them. If they turn 22 on 12/31, exclude them.

So they have to be 12-21 for the full duration of the year? So, for example, someone born on 2005-01-01 would be included for this year, however, if they were born on 2017-01-02 they would not? Also, someone born on 1995-12-31 would not be included. Correct?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

NineIron SSChampion Points: 12537 More actions · Answer 4

NineIron

SSChampion

Points: 12537

November 9, 2017 at 9:27 am

#1967417

Correct.

Thom A SSC Guru Points: 99134 More actions · Answer 5

Couple of answers, might be a better way, but it's late in the day:

DECLARE @Year int = 2017; --I'm going to assume you will just be provided an int for the year
DECLARE @YearStart date = CONVERT(date, CONVERT(varchar(4),@Year)),
   @YearEnd date = DATEADD(DAY, -1, CONVERT(date, CONVERT(varchar(4),@Year + 1)));
WITH Age AS (
  SELECT *,
    DATEDIFF(YEAR, DOB, @YearStart) - --As people have Birthday's during the year, but need to include those born on 01 Jan
    CASE WHEN DOB < DATEADD(YEAR, DATEDIFF(YEAR, DOB, @YearStart),DOB) THEN 1 ELSE 0 END AS AgeAtStart,
    DATEDIFF(YEAR, DOB, @YearEnd) AS AgeAtEnd
  FROM #T)
SELECT *
FROM Age
WHERE AgeAtStart BETWEEN 12 AND 21
 AND AgeAtEnd BETWEEN 12 AND 21;
WITH Age AS (
  SELECT *,
    DATEDIFF(YEAR, DOB, @YearEnd) AS AgeAtEnd
  FROM #T)
SELECT *
FROM Age
WHERE AgeAtEnd BETWEEN 13 AND 21
 OR (AgeAtEnd = 12 AND DATEPART(DAY, DOB) = 1 AND DATEPART(MONTH, DOB) = 1);

This isn't tested with all your test date, as the dates are Americanised (and there aren't 27 month's in a year 😉 ).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

drew.allen SSC Guru Points: 76999 More actions · Answer 6

People usually approach these problems the wrong way. They try to determine if someone falls within a certain age range based on their birthdate when it's actually much easier to determine which birthdates fall within the range based on the ages on a given date. The following may need to be tweaked to get the correct dates, but this gives you the basic approach.

DECLARE @MinDOB DATE = DATEADD(YEAR, DATEDIFF(YEAR, '1922-12-31', GETDATE()), '1900-12-31') -- Turns 22 on the last day of the year , @MaxDOB DATE = DATEADD(YEAR, DATEDIFF(YEAR, '1912-01-01', GETDATE()), '1900-01-01') -- Turns 12 on first day of year

SELECT * FROM #T WHERE DOB > @MinDOB AND DOB <= @MaxDOB

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Thom A SSC Guru Points: 99134 More actions · Answer 7

drew.allen - Thursday, November 9, 2017 9:51 AM
People usually approach these problems the wrong way. They try to determine if someone falls within a certain age range based on their birthdate when it's actually much easier to determine which birthdates fall within the range based on the ages on a given date. The following may need to be tweaked to get the correct dates, but this gives you the basic approach.
DECLARE @MinDOB DATE = DATEADD(YEAR, DATEDIFF(YEAR, '1922-12-31', GETDATE()), '1900-12-31') -- Turns 22 on the last day of the year , @MaxDOB DATE = DATEADD(YEAR, DATEDIFF(YEAR, '1912-01-01', GETDATE()), '1900-01-01') -- Turns 12 on first day of year
SELECT * FROM #T WHERE DOB > @MinDOB AND DOB <= @MaxDOB
Drew

Interesting solution, Drew. Does appear to be marginally faster (26ms compared to 93ms for about 148K records).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 8

Using datetime in the DOB column makes it a bit more confusing, but it looks like you're considering any point in the day as the whole day? If so and as Thom mentioned they are the age for the entire year then maybe:
DECLARE @Year int = 2017; DECLARE @StartDate datetime = DATEADD(YEAR, -21, CONVERT(date, CONVERT(varchar(4),@Year + 1))), @EndDate datetime = DATEADD(YEAR, -12, CONVERT(date, CONVERT(varchar(4),@Year)));

SELECT PatientID, DOB FROM #T WHERE DOB >= @StartDate AND DOB < @EndDate;

edit: D'OH, Drew beat me too it

NineIron SSChampion Points: 12537 More actions · Answer 9

NineIron

SSChampion

Points: 12537

November 9, 2017 at 11:02 am

#1967432

Thanx folks.

drew.allen SSC Guru Points: 76999 More actions · Answer 10

Thom A - Thursday, November 9, 2017 10:01 AM
drew.allen - Thursday, November 9, 2017 9:51 AM
People usually approach these problems the wrong way. They try to determine if someone falls within a certain age range based on their birthdate when it's actually much easier to determine which birthdates fall within the range based on the ages on a given date. The following may need to be tweaked to get the correct dates, but this gives you the basic approach.
DECLARE @MinDOB DATE = DATEADD(YEAR, DATEDIFF(YEAR, '1922-12-31', GETDATE()), '1900-12-31') -- Turns 22 on the last day of the year , @MaxDOB DATE = DATEADD(YEAR, DATEDIFF(YEAR, '1912-01-01', GETDATE()), '1900-01-01') -- Turns 12 on first day of year
SELECT * FROM #T WHERE DOB > @MinDOB AND DOB <= @MaxDOB
Drew
Interesting solution, Drew. Does appear to be marginally faster (26ms compared to 93ms for about 148K records).

Less than a third of the time is "marginally faster"? 😀

And did you try that with or without an index on DOB?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 11

drew.allen - Thursday, November 9, 2017 11:04 AM

Less than a third of the time is "marginally faster"? 😀

hey... 60% of the time, it works every time! 😉