Calculate Age (in years)

  • Alan Burstein

    SSC Guru

    Points: 61079

    Comments posted to this topic are about the item Calculate Age (in years)

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Jeff Moden

    SSC Guru

    Points: 996502

    From the Article:


    See Comments in the code for more details.

    Awesome. I don't very often see such a useful bit of header information. You've provided a succinct yet accurate purpose, provided some usage examples, gave credit to your resources, documented places to get more information, and documented the revisions. Nicely done, Alan.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Forum FAQ

  • Ed Wagner

    SSC Guru

    Points: 286980

    Nice job, Alan. It may be sightly more complicated (but not much more) than an SF, but I'll take performance over simplicity every time. After all, when you multiply your savings by how many executions you have per day, it really adds up fast.

  • MadAdmin

    SSChampion

    Points: 11260

    What should the results for these test cases be?

    SELECT age FROM dbo.YearsApart('2/28/2002','02/28/2003');

    SELECT age FROM dbo.YearsApart('2/28/2003','02/28/2004');

    SELECT age FROM dbo.YearsApart('2/29/2004','02/28/2005');

    SELECT age FROM dbo.YearsApart('03/01/2004','03/01/2005');

    Edit : Seems that how you handle leaplings depends on your country.

    In US, Hong Kong, Taiwan and New Zealand, dbo.YearsApart('2/29/2004','02/28/2005'); should be 1

    In England and Wales, dbo.YearsApart('2/29/2004','02/28/2005'); should be zero.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Interesting way to do this. I'll need to compare with our method. Thanks.

  • Alan Burstein

    SSC Guru

    Points: 61079

    MadAdmin (4/26/2016)


    What should the results for these test cases be?

    SELECT age FROM dbo.YearsApart('2/28/2002','02/28/2003');

    SELECT age FROM dbo.YearsApart('2/28/2003','02/28/2004');

    SELECT age FROM dbo.YearsApart('2/29/2004','02/28/2005');

    SELECT age FROM dbo.YearsApart('03/01/2004','03/01/2005');

    Edit : Seems that how you handle leaplings depends on your country.

    In US, Hong Kong, Taiwan and New Zealand, dbo.YearsApart('2/29/2004','02/28/2005'); should be 1

    In England and Wales, dbo.YearsApart('2/29/2004','02/28/2005'); should be zero.

    Thank you for the post. The logic (which is open to debate) is that leap years are a day longer than non leap years. For non leap years something has aged one year on the 365th day, for leap years it has aged one year on the 366th day.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Alan Burstein

    SSC Guru

    Points: 61079

    Jeff Moden (4/10/2016)


    From the Article:


    See Comments in the code for more details.

    Awesome. I don't very often see such a useful bit of header information. You've provided a succinct yet accurate purpose, provided some usage examples, gave credit to your resources, documented places to get more information, and documented the revisions. Nicely done, Alan.

    Ed Wagner (4/10/2016)


    Nice job, Alan. It may be sightly more complicated (but not much more) than an SF, but I'll take performance over simplicity every time. After all, when you multiply your savings by how many executions you have per day, it really adds up fast.

    Thank you both very much! That means a lot.

    I stole the comment section layout/formatting from you Jeff 😛

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 7 posts - 1 through 7 (of 7 total)

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