Calculating Age
-
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
-
Richard Briggs (3/13/2009)
Hey Comradeshow 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
-
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."
-
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 -
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 -
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
-
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
-
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
-
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 -
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.
-
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
-
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 -
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
-
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 -
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."
Viewing 15 posts - 46 through 60 (of 95 total)
You must be logged in to reply to this topic. Login to reply