Find patients with age 6 months to 5 years

• NineIron

  SSChampion

  Points: 12529

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

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

  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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

• NineIron

  SSChampion

  Points: 12529

  More actions

  March 3, 2015 at 9:26 am

  #1781415

  Yes. Age on the appointment date.

• NineIron

  SSChampion

  Points: 12529

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

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

  More actions

  March 3, 2015 at 10:14 am

  #1781442

  Wonderful. Thank you very much.