Filtering on (specific) Age .... 16, 21, 25, 65, etc

• ian.hadlington

  SSC Veteran

  Points: 232

  More actions

  July 9, 2009 at 2:46 am

  #131875

  I've had a fairly good look (google) through various scripts about age, as it is somewhat problematic, since:

  a) person records will generally hold DOB, as this is far more static than age,

  b) DateDiff(dd, @dob, @CurrDate)/365.25 is, at best, a close approximation.

  c) DateDiff(yy, @dob, @CurrDate) ... but -1 if month(@CurrDate)Select * from PersonDetails WHERE dob <= DateAdd(yy, -18, @CurrDate)
  Immediately, we can see the brevity and, additionally, we remove the functional processing of table fields, thus improving performance.

  Extending this further to ranges:

  DECLARE @RangeLimit0 SmallDateTime
  DECLARE @RangeLimit5 SmallDateTime
  DECLARE @RangeLimit10 SmallDateTime
  DECLARE @RangeLimit20 SmallDateTime
  DECLARE @RangeLimit30 SmallDateTime

  SET @RangeLimit0 = @CurrDate
  SET @RangeLimit5 = DateAdd (yy, -5, @CurrDate)
  SET @RangeLimit10 = DateAdd (yy, -10, @CurrDate)
  SET @RangeLimit20 = DateAdd (yy, -20, @CurrDate)

  Then we can group records thus:

  Select * from PersonDetails WHERE dob <= @RangeLimit0 and dob > @RangeLimit5

  etc.

  Surely I'm not the first person to come up with this???

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  July 9, 2009 at 3:19 am

  #1022226

  Hi Ian,

  I pretty much agree with your logic , im not sure what your question is though ?

  ---

  
  Clear Sky SQL
  My Blog[/url]