Calculating Age

Question

Post reply

Calculating Age

Viewing 15 posts - 46 through 60 (of 93 total)

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

jpratt-797544 SSC Eights! Points: 847 More actions · Answer 1

This was perfect timing, Lynn, since I need to produce an Age column in a view for patients in our medical facility. Since I only need to know the age in years, I considered your points and came up with the following SQL (examples for a newborn and very old person included):

SELECT "AgeInYears" = DATEDIFF(month, '12/31/1908', '1/1/2009') / 12

SELECT "AgeInYears" = DATEDIFF(month, '12/31/2008', '1/1/2009') / 12

To be ultra safe, you could wrap the DATEDIFF in FLOOR, but the equation seems to round down by itself (I tested with SQL 2005 std SP2 32-bit, and SQL 2000 ent SP4 32-bit). No one seemed to be doing the divide by 12 bit, and I'm leary of using a day-based/divide by 365.25 solution since a calendar year is either 365 or 366 days long but is always 12 months.

Thanks for the article.

J Pratt

jpratt-797544 SSC Eights! Points: 847 More actions · Answer 2

Richard Briggs (3/13/2009)

Hey Comrades

how about creating a Peristed Calulated column in your 'person' table.

The column would call one of the function solutions you have discussed.

This makes is easier / quicker to list (say) people older than 50.

I think if you had a databse of say 20 millions patients (health care type sysem) the performance boost of not having to calulate age in the query would be advantageous.

Brigzy

An outstanding idea *if* you have a table to store the data in. I have to use a calculated column in a view since I can't change the software vendor's source table.

J Pratt

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 3

jpratt (3/13/2009)

This was perfect timing, Lynn, since I need to produce an Age column in a view for patients in our medical facility. Since I only need to know the age in years, I considered your points and came up with the following SQL (examples for a newborn and very old person included):

SELECT "AgeInYears" = DATEDIFF(month, '12/31/1908', '1/1/2009') / 12

SELECT "AgeInYears" = DATEDIFF(month, '12/31/2008', '1/1/2009') / 12

To be ultra safe, you could wrap the DATEDIFF in FLOOR, but the equation seems to round down by itself (I tested with SQL 2005 std SP2 32-bit, and SQL 2000 ent SP4 32-bit). No one seemed to be doing the divide by 12 bit, and I'm leary of using a day-based/divide by 365.25 solution since a calendar year is either 365 or 366 days long but is always 12 months.

Thanks for the article.

jpratt - something to be considered if you're categorizing for your report across a large patient base - Do you need to keep a patient within one and only one age band? i.e. if you're looking at a quarter's worth, or year's worth of data, do you need to age a patient up (or down) if they have services in Q1 and Q4, but changed age ranges in the middle. Otherwise you could be double-counting patients.

Something I've run across from the insurance claim perspective.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

C# Gnu SSCertifiable Points: 5973 More actions · Answer 4

Comrades,

as regards my own post on creating a CALCULATED PERSISTED column containing 'Age'.

I have discovered that actually because GetDate() is non-deterministic such a column cannot be made to PERSISTED.

It is the fact that it is Persisted that would give me the performance benefit I would demand.

Therefore I now have a new column Age im my Person table that is re-calulated by an SQL Server job, this runs shortly after midnight - using an UPDATE statement to recalcualte the Age for all records in my Person database.

Now that I have this column (remember I am talking BIG database here many millions rows) I am able to index the column and my Query (WHERE Age > 50) is v.fast!;)

Brigzy

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

C# Gnu SSCertifiable Points: 5973 More actions · Answer 5

Richard Briggs (3/13/2009)

Comrades,

as regards my own post on creating a CALCULATED PERSISTED column containing 'Age'.

I have discovered that actually because GetDate() is non-deterministic such a column cannot be made to PERSISTED.

It is the fact that it is Persisted that would give me the performance benefit I would demand.

Therefore I now have a new column Age im my Person table that is re-calulated by an SQL Server job, this runs shortly after midnight - using an UPDATE statement to recalcualte the Age for all records in my Person database.

Now that I have this column (remember I am talking BIG database here many millions rows) I am able to index the column and my Query (WHERE Age > 50) is v.fast!;)

Brigzy

And Comrades I use :

-- ==================================================



-- Author:	R.Briggs

-- Create date:     13/3/09

-- Description:	Return age from DOB

--

-- Age today:

-- SELECT fn_GetAge (DateOfBirth, '25 December 2009 ') FROM Person

--

-- Age on Christmas day:

-- SELECT fn_GetAge (DateOfBirth, '25 December 2009 ') FROM Person

--

-- ==================================================

CREATE FUNCTION [dbo].[fn_GetAge] ( @pDateOfBirth    DATETIME,

                                     @pAsOfDate       DATETIME )

RETURNS INT

AS

BEGIN

    DECLARE @intAge         INT

    IF @pDateOfBirth >= @pAsOfDate

        RETURN 0

    SET @intAge = DATEDIFF(YY, @pDateOfBirth, @pAsOfDate)

    IF MONTH(@pDateOfBirth) > MONTH(@pAsOfDate) OR

      (MONTH(@pDateOfBirth) = MONTH(@pAsOfDate) AND

       DAY(@pDateOfBirth)   > DAY(@pAsOfDate))

        SET @intAge = @intAge - 1

    RETURN @intAge

END

If you think its wrong - Please let me know!

Brigzy

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Andrew in WV SSCommitted Points: 1655 More actions · Answer 6

Richard Briggs (3/13/2009)

Comrades,

as regards my own post on creating a CALCULATED PERSISTED column containing 'Age'.

I have discovered that actually because GetDate() is non-deterministic such a column cannot be made to PERSISTED.

It is the fact that it is Persisted that would give me the performance benefit I would demand.

Therefore I now have a new column Age im my Person table that is re-calulated by an SQL Server job, this runs shortly after midnight - using an UPDATE statement to recalcualte the Age for all records in my Person database.

Now that I have this column (remember I am talking BIG database here many millions rows) I am able to index the column and my Query (WHERE Age > 50) is v.fast!;)

Brigzy

Would you not get the same benefit by approaching your query from the other side without the cost of updating "many millions" of rows every night? For any given date, it should be easy enough to calculate the minimum and maximum dates that will yield the age you are seeking, and then query patients whose DOB is between those dates. DOB could be indexed to give you the boost you're after.

This is untested, but I think something like this should work:



DECLARE @age tinyint

DECLARE @range_begin_date datetime

DECLARE @range_end_date datetime

SET @age = 50

SELECT  @range_begin_date = DATEADD(yy,  -@age - 1, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)),

        @range_end_date = DATEADD(yy, -@age, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))

SELECT  @range_begin_date, @range_end_date

SELECT  *

FROM    some_table

WHERE   dob > @range_begin_date

  AND   dob <= @range_end_date

Andrew

--Andrew

ascott-787611 SSC Enthusiast Points: 163 More actions · Answer 7

ascott-787611

SSC Enthusiast

Points: 163

March 13, 2009 at 8:16 am

#959130

This is just what I have been looking for! I could have done it myself, but why reinvent the wheel, when you can buy one off the shelf

GSquared SSC Guru Points: 260824 More actions · Answer 8

Good article, Lynn. Glad you mentioned the legal issues surrounding Leaplings.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

C# Gnu SSCertifiable Points: 5973 More actions · Answer 9

Andrew (3/13/2009)

Richard Briggs (3/13/2009)

Comrades,

as regards my own post on creating a CALCULATED PERSISTED column containing 'Age'.

I have discovered that actually because GetDate() is non-deterministic such a column cannot be made to PERSISTED.

It is the fact that it is Persisted that would give me the performance benefit I would demand.

Therefore I now have a new column Age im my Person table that is re-calulated by an SQL Server job, this runs shortly after midnight - using an UPDATE statement to recalcualte the Age for all records in my Person database.

Now that I have this column (remember I am talking BIG database here many millions rows) I am able to index the column and my Query (WHERE Age > 50) is v.fast!;)

Brigzy

Would you not get the same benefit by approaching your query from the other side without the cost of updating "many millions" of rows every night? For any given date, it should be easy enough to calculate the minimum and maximum dates that will yield the age you are seeking, and then query patients whose DOB is between those dates. DOB could be indexed to give you the boost you're after.

This is untested, but I think something like this should work:

DECLARE @age tinyint DECLARE @range_begin_date datetime DECLARE @range_end_date datetime SET @age = 50 SELECT @range_begin_date = DATEADD(yy, -@age - 1, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)), @range_end_date = DATEADD(yy, -@age, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) SELECT @range_begin_date, @range_end_date SELECT * FROM some_table WHERE dob > @range_begin_date AND dob <= @range_end_date

Andrew

Andrew that is nice.

I do also have a fair number of screens/reports where lists of records need to display age, so for my purposes its easier for me to have Age pre-calced.

I have to produce some stats too where I will age-banding, again (for my app at least) it will be easier/faster pre-calculated.

Like your code solution ++good (Orwell)

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 10

Richard Briggs (3/13/2009)

Richard Briggs (3/13/2009)

Comrades,

as regards my own post on creating a CALCULATED PERSISTED column containing 'Age'.

I have discovered that actually because GetDate() is non-deterministic such a column cannot be made to PERSISTED.

It is the fact that it is Persisted that would give me the performance benefit I would demand.

Therefore I now have a new column Age im my Person table that is re-calulated by an SQL Server job, this runs shortly after midnight - using an UPDATE statement to recalcualte the Age for all records in my Person database.

Now that I have this column (remember I am talking BIG database here many millions rows) I am able to index the column and my Query (WHERE Age > 50) is v.fast!;)

Brigzy

And Comrades I use :

-- ==================================================
-- Author: R.Briggs -- Create date: 13/3/09 -- Description: Return age from DOB -- -- Age today: -- SELECT fn_GetAge (DateOfBirth, '25 December 2009 ') FROM Person -- -- Age on Christmas day: -- SELECT fn_GetAge (DateOfBirth, '25 December 2009 ') FROM Person -- -- ================================================== CREATE FUNCTION [dbo].[fn_GetAge] ( @pDateOfBirth DATETIME, @pAsOfDate DATETIME ) RETURNS INT AS BEGIN DECLARE @intAge INT IF @pDateOfBirth >= @pAsOfDate RETURN 0 SET @intAge = DATEDIFF(YY, @pDateOfBirth, @pAsOfDate) IF MONTH(@pDateOfBirth) > MONTH(@pAsOfDate) OR (MONTH(@pDateOfBirth) = MONTH(@pAsOfDate) AND DAY(@pDateOfBirth) > DAY(@pAsOfDate)) SET @intAge = @intAge - 1 RETURN @intAge
END

If you think its wrong - Please let me know!

Brigzy

Please tell me that you ARE NOT updating every row in your database every night.

GSquared SSC Guru Points: 260824 More actions · Answer 11

I just did a pretty heavy-duty test of the YYYYMMDD method, and it seems to be plenty fast and was accurate for all dates I checked.

Here's the test:



set nocount on;

if object_id(N'tempdb..#T') is not null

	drop table #T;

create table #T (

BDay datetime primary key,

Date datetime,

Age as (cast(convert(varchar(25), Date, 112) as int)-cast(convert(varchar(25), bday, 112) as int))/10000);

insert into #T (BDay)

select dateadd(day, Number, '1/1/1800')

from dbo.Numbers;

insert into #T (BDay)

select dateadd(day, Number, '1936-11-24 00:00:00.000')

from dbo.Numbers;

update #T

set Date = dateadd(day, abs(checksum(newid()))%(125*365), BDay);

That gives semi-random ages for a full range of birthdays for the last two centuries and a significant part of this one.

I checked a bunch of things with it, like leap years, Dec dates for Jan B-days, dates with the same month but the date is less than the birthday, and so on. Seemed to be accurate in everything I could throw at it.

I don't have the time right now for a mathematical proof, but it does seem to work.

I hadn't run into that method before, so thank you to the people who brought it up.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

C# Gnu SSCertifiable Points: 5973 More actions · Answer 12

Lynn Pettis (3/13/2009)

Richard Briggs (3/13/2009)

Richard Briggs (3/13/2009)

Comrades,

as regards my own post on creating a CALCULATED PERSISTED column containing 'Age'.

I have discovered that actually because GetDate() is non-deterministic such a column cannot be made to PERSISTED.

It is the fact that it is Persisted that would give me the performance benefit I would demand.

Therefore I now have a new column Age im my Person table that is re-calulated by an SQL Server job, this runs shortly after midnight - using an UPDATE statement to recalcualte the Age for all records in my Person database.

Now that I have this column (remember I am talking BIG database here many millions rows) I am able to index the column and my Query (WHERE Age > 50) is v.fast!;)

Brigzy

And Comrades I use :

-- ==================================================
-- Author: R.Briggs -- Create date: 13/3/09 -- Description: Return age from DOB -- -- Age today: -- SELECT fn_GetAge (DateOfBirth, '25 December 2009 ') FROM Person -- -- Age on Christmas day: -- SELECT fn_GetAge (DateOfBirth, '25 December 2009 ') FROM Person -- -- ================================================== CREATE FUNCTION [dbo].[fn_GetAge] ( @pDateOfBirth DATETIME, @pAsOfDate DATETIME ) RETURNS INT AS BEGIN DECLARE @intAge INT IF @pDateOfBirth >= @pAsOfDate RETURN 0 SET @intAge = DATEDIFF(YY, @pDateOfBirth, @pAsOfDate) IF MONTH(@pDateOfBirth) > MONTH(@pAsOfDate) OR (MONTH(@pDateOfBirth) = MONTH(@pAsOfDate) AND DAY(@pDateOfBirth) > DAY(@pAsOfDate)) SET @intAge = @intAge - 1 RETURN @intAge
END

If you think its wrong - Please let me know!

Brigzy

Please tell me that you ARE NOT updating every row in your database every night.

It takes sub 7 seconds when db out of use - but I agree it against my principles too.

As I say you cannot persist a column that uses a function based on GetDate().

I also have many developers who might have their own ways of calculating Age - as this thread clearly demonstrates! My solution ensures my data is not subject to any such variations to calulation method.

Brigzy

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Prashant-280564 Grasshopper Points: 20 More actions · Answer 13

after doing my first ever post in life here I was bit disappointed from my own solution so here is the inspired from previous posts.

DECLARE @BirthDate datetime

DECLARE @CurrentDate datetime

SELECT @BirthDate = '01 Jan 2008'

SELECT @CurrentDate = '01 Jan 2009'

SELECT CASE WHEN @BirthDate > @CurrentDate THEN 0

ELSE ((DATEDIFF(mm,@BirthDate,@CurrentDate) - (CASE WHEN (DAY(@CurrentDate)-DAY(@BirthDate)) >= 0 THEN 0 ELSE 1 END)) /12) END

C# Gnu SSCertifiable Points: 5973 More actions · Answer 14

Comrades,

Around one's birthday, we can be 16 in the UK, but still 15 in the US.

Worth mentioning as may be relevant to certain age verification web applications.

Brigzy

16:36 UK GMT

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 15

Richard Briggs (3/13/2009)

Comrades,

Around one's birthday, we can be 16 in the UK, but still 15 in the US.

Worth mentioning as may be relevant to certain age verification web applications.

Brigzy

16:36 UK GMT

sp_MSGoBackInTime strikes again. How can you possibly be 16 in the UK and 15 in the US, unless you're talking about the timezone difference?

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."