Calculate Age (in years)

Question

Calculate Age (in years)

Alan Burstein

SSC Guru

Points: 61141
More actions
April 6, 2016 at 3:09 pm

#306255

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

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

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

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 1

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)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

MadAdmin SSChampion Points: 11261 More actions · Answer 3

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 More actions · Answer 4

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

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: 61141 More actions · Answer 6

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