Find patients with age 6 months to 5 years

• NineIron

  SSChampion

  Points: 12536

  More actions

  March 3, 2015 at 9:03 am

  #315875

  Can you help with the formula to return patients ages 6 months to 5 years?

  create table dbo.TEST

  (

  MRN varchar(10),

  DOB datetime,

  ApptDt2 datetime

  )

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '335409','2010-05-16 00:00:00.000','2014-07-11 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '336320','2010-10-04 00:00:00.000','2014-09-24 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '336323','2010-08-14 00:00:00.000','2014-07-31 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '336392','2010-09-06 00:00:00.000','2014-10-15 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '336909','2010-07-08 00:00:00.000','2014-07-17 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '337289','2010-11-16 00:00:00.000','2014-12-29 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '337290','2010-11-16 00:00:00.000','2014-12-29 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '337538','2010-10-18 00:00:00.000','2014-10-27 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '337721','2011-01-05 00:00:00.000','2014-08-29 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '339406','2011-02-16 00:00:00.000','2014-09-11 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '340472','2011-09-01 00:00:00.000','2014-09-17 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '341017','2011-10-26 00:00:00.000','2014-11-11 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '341585','2011-06-28 00:00:00.000','2014-08-08 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '344977','2014-08-05 00:00:00.000','2014-10-06 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '344977','2014-08-05 00:00:00.000','2014-12-05 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '345001','2014-08-28 00:00:00.000','2014-11-04 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '345030','2012-04-01 00:00:00.000','2014-12-04 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '346487','2012-08-08 00:00:00.000','2014-08-08 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '346972','2012-10-17 00:00:00.000','2014-11-25 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '346980','2012-09-23 00:00:00.000','2014-09-24 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '347047','2012-11-07 00:00:00.000','2014-07-28 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '350288','2012-11-01 00:00:00.000','2014-11-03 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '350298','2012-12-07 00:00:00.000','2014-12-17 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '350346','2013-01-07 00:00:00.000','2014-07-14 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '351077','2013-04-14 00:00:00.000','2014-07-24 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '351077','2013-04-14 00:00:00.000','2014-08-06 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '351077','2013-04-14 00:00:00.000','2014-11-06 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '351124','2013-05-11 00:00:00.000','2014-11-13 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '351124','2013-05-11 00:00:00.000','2014-12-11 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '351254','2013-08-24 00:00:00.000','2014-12-10 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '351782','2013-09-06 00:00:00.000','2014-09-10 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '351858','2013-10-17 00:00:00.000','2014-07-21 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '351858','2013-10-17 00:00:00.000','2014-10-22 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '351981','2014-01-12 00:00:00.000','2014-07-28 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '351981','2014-01-12 00:00:00.000','2014-10-29 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '352416','2013-11-25 00:00:00.000','2014-12-08 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '352759','2014-02-02 00:00:00.000','2014-11-21 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '352858','2014-02-22 00:00:00.000','2014-09-04 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '352858','2014-02-22 00:00:00.000','2014-11-06 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '353568','2014-09-30 00:00:00.000','2014-12-03 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '832360','2009-09-23 00:00:00.000','2014-09-23 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '833181','2009-10-09 00:00:00.000','2014-08-06 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '833209','2009-09-18 00:00:00.000','2014-08-05 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '833358','2009-10-13 00:00:00.000','2014-08-25 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '833474','2009-10-23 00:00:00.000','2014-09-29 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '833666','2009-10-27 00:00:00.000','2014-08-05 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '834004','2009-11-04 00:00:00.000','2014-10-29 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '834182','2009-11-15 00:00:00.000','2014-11-13 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '834486','2009-11-30 00:00:00.000','2014-08-29 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '834518','2009-12-01 00:00:00.000','2014-09-12 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '834638','2009-12-05 00:00:00.000','2014-07-29 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '834683','2009-12-04 00:00:00.000','2014-07-15 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '834940','2009-12-12 00:00:00.000','2014-07-29 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '834963','2009-12-15 00:00:00.000','2014-09-08 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '835084','2009-12-25 00:00:00.000','2014-10-09 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '835495','2010-01-13 00:00:00.000','2014-09-30 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '835616','2010-01-19 00:00:00.000','2014-12-29 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '835881','2010-01-25 00:00:00.000','2014-11-10 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '835986','2010-01-28 00:00:00.000','2014-08-27 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '835997','2010-02-02 00:00:00.000','2014-09-24 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '836041','2010-02-06 00:00:00.000','2014-09-04 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '836210','2010-02-11 00:00:00.000','2014-07-03 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '836266','2010-02-16 00:00:00.000','2014-09-03 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '836557','2010-02-23 00:00:00.000','2014-10-28 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '836719','2009-10-25 00:00:00.000','2014-10-14 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '836730','2009-12-18 00:00:00.000','2014-12-04 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '836788','2010-03-04 00:00:00.000','2014-09-04 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '836887','2010-03-08 00:00:00.000','2014-08-18 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '837158','2010-03-16 00:00:00.000','2014-12-04 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '837302','2010-03-23 00:00:00.000','2014-10-21 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '837308','2010-03-26 00:00:00.000','2014-07-11 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '837328','2010-03-25 00:00:00.000','2014-08-06 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '837335','2010-03-26 00:00:00.000','2014-11-24 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '837353','2010-03-24 00:00:00.000','2014-12-15 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '837362','2010-03-26 00:00:00.000','2014-09-23 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '837408','2010-03-25 00:00:00.000','2014-07-21 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '837657','2010-04-07 00:00:00.000','2014-09-08 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '837668','2010-04-08 00:00:00.000','2014-09-24 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '837669','2010-04-07 00:00:00.000','2014-08-18 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '837706','2010-04-08 00:00:00.000','2014-09-29 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '837707','2010-04-09 00:00:00.000','2014-07-30 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '837711','2010-04-12 00:00:00.000','2014-10-31 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '837818','2010-03-25 00:00:00.000','2014-09-12 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '837897','2010-03-01 00:00:00.000','2014-08-26 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '837915','2010-04-16 00:00:00.000','2014-09-24 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '837946','2010-04-20 00:00:00.000','2014-08-11 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '837992','2010-04-21 00:00:00.000','2014-09-09 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '838017','2010-04-24 00:00:00.000','2014-07-15 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '838070','2010-04-25 00:00:00.000','2014-08-29 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '838091','2010-04-20 00:00:00.000','2014-07-09 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '838144','2010-04-29 00:00:00.000','2014-11-07 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '838223','2010-04-27 00:00:00.000','2014-07-29 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '838230','2010-05-04 00:00:00.000','2014-07-31 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '838269','2010-05-04 00:00:00.000','2014-07-30 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '838312','2010-03-01 00:00:00.000','2014-08-19 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '838314','2010-05-03 00:00:00.000','2014-08-04 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '838484','2010-05-12 00:00:00.000','2014-12-04 00:00:00.000')

  insert into TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '838621','2010-05-19 00:00:00.000','2014-11-26 00:00:00.000')

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  March 3, 2015 at 9:18 am

  #1781408

  Do you mean at the date of the appointment?

  SELECT *

  FROM TEST

  WHERE DOB BETWEEN DATEADD(YYYY, -5, ApptDt2) AND DATEADD(MM, -6, ApptDt2)

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• ScottPletcher

  SSC Guru

  Points: 101098

  More actions

  March 3, 2015 at 9:22 am

  #1781409

  Luis Cazares (3/3/2015)

  ---

  Do you mean at the date of the appointment?

  SELECT *

  FROM TEST

  WHERE DOB BETWEEN DATEADD(YYYY, -5, ApptDt2) AND DATEADD(MM, -6, ApptDt2)

  Wouldn't you actually need - 6 years + 1 day?

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• NineIron

  SSChampion

  Points: 12536

  More actions

  March 3, 2015 at 9:26 am

  #1781415

  Yes. Age on the appointment date.

• NineIron

  SSChampion

  Points: 12536

  More actions

  March 3, 2015 at 9:28 am

  #1781416

  Can you add a field that will show the age?

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  March 3, 2015 at 10:08 am

  #1781440

  Something like this?

  SELECT *,

  CASE WHEN DATEADD(MM, DATEDIFF(MM, DOB, ApptDt2), DOB) > ApptDt2

  THEN DATEDIFF(MM, DOB, ApptDt2) - 1

  ELSE DATEDIFF(MM, DOB, ApptDt2) END / 12 AS Years,

  CASE WHEN DATEADD(MM, DATEDIFF(MM, DOB, ApptDt2), DOB) > ApptDt2

  THEN DATEDIFF(MM, DOB, ApptDt2) - 1

  ELSE DATEDIFF(MM, DOB, ApptDt2) END % 12 AS Months

  FROM TEST

  WHERE DOB BETWEEN DATEADD( DD, 1, DATEADD(YYYY, -6, ApptDt2)) AND DATEADD(MM, -6, ApptDt2)

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• NineIron

  SSChampion

  Points: 12536

  More actions

  March 3, 2015 at 10:14 am

  #1781442

  Wonderful. Thank you very much.