Find patients ages 13 - 21 as of calendar year

• NineIron

  SSChampion

  Points: 12529

  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')

• Thom A

  SSC Guru

  Points: 99126

  More actions

  November 9, 2017 at 9:12 am

  #1967412

  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: 12529

  More actions

  November 9, 2017 at 9:16 am

  #1967413

  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: 99126

  More actions

  November 9, 2017 at 9:20 am

  #1967414

  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: 12529

  More actions

  November 9, 2017 at 9:27 am

  #1967417

  Correct.

• Thom A

  SSC Guru

  Points: 99126

  More actions

  November 9, 2017 at 9:46 am

  #1967419

  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: 76948

  More actions

  November 9, 2017 at 9:51 am

  #1967421

  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: 99126

  More actions

  November 9, 2017 at 10:01 am

  #1967422

  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

  November 9, 2017 at 10:02 am

  #1967423

  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: 12529

  More actions

  November 9, 2017 at 11:02 am

  #1967432

  Thanx folks.

• drew.allen

  SSC Guru

  Points: 76948

  More actions

  November 9, 2017 at 11:04 am

  #1967433

  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

  November 9, 2017 at 11:34 am

  #1967438

  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! 😉