Calculating Age

  • IceDread

    SSCertifiable

    Points: 5020

    C# Gnu (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

    Old thread awoken here but it was a good hit I got when searching on the issue.

    Would this not be faster? It's your code but modified so the performance hit of a scalar function should not interfere.

    CREATE FUNCTION [dbo].[GetAge]

    (

    @DateOfBirth DATETIME,

    @AsOfDate DATETIME

    )

    RETURNS TABLE

    AS

    RETURN

    (

    select case when @DateOfBirth >= @AsOfDate then 0

    when MONTH(@DateOfBirth) > MONTH(@AsOfDate)

    OR (MONTH(@DateOfBirth) = MONTH(@AsOfDate)

    AND DAY(@DateOfBirth) > DAY(@AsOfDate))

    then DATEDIFF(YY, @DateOfBirth, @AsOfDate) -1

    else DATEDIFF(YY, @DateOfBirth, @AsOfDate)

    end

    as Age

    )

    GO

  • Manie Verster

    SSCertifiable

    Points: 7022

    Thank you Richard, you have just solved a problem for me of an old problem of giving a valid age. Yes, I know there are most probably many solutions out there but the problem has not been serious enough to actually do something about it and Lynn also had a very good solution in her article but you script just put the cherry on the cake.

    Thanks a mil.:-D:-D:-D:-D:-D

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • Lynn Pettis

    SSC Guru

    Points: 442360

    Manie Verster (2/24/2016)


    Thank you Richard, you have just solved a problem for me of an old problem of giving a valid age. Yes, I know there are most probably many solutions out there but the problem has not been serious enough to actually do something about it and Lynn also had a very good solution in her article but you script just put the cherry on the cake.

    Thanks a mil.:-D:-D:-D:-D:-D

    I just have to correct you, I am a guy.

  • Manie Verster

    SSCertifiable

    Points: 7022

    Sorry Lynn, my bad!:blush:

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

Viewing 4 posts - 91 through 94 (of 94 total)

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