Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pingme
Starting Member
4 Posts |
 Posted - 2009-03-24 : 06:36:17
|
| Can anyone help me here please.Trying to calculate age for an entire table.It's an enormous database, so there's no quetion of going row by row.babsonoon |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-03-24 : 06:38:44
|
| Calculating age from what exactly? If it is someones age, then surely a datediff will do the job? |
 |
|
|
pingme
Starting Member
4 Posts |
Posted - 2009-03-24 : 07:09:52
|
Thanks RickD,I actually need to first derive ages for people. I have the birth year and the current year. Another task is to enter the values i get into the entire column for all the entires.Your help is highly appreciated.quote:
Originally posted by RickD
Calculating age from what exactly? If it is someones age, then surely a datediff will do the job?
babsonoon |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-03-24 : 07:35:04
|
| You have 2 options 1. a calculated column in the table to calculate each age for each row. 2. use a VIEW to display the age for each row. Which you use should be decided by how often this will be used, and does table have a high proportion of INSERT / UPDATE or mostly SELECTs. Calculated column takes up space etc but can be queried quicker to select than view (although you could index the view), but they will slow inserts and updates on the table. |
|
|
|
pingme
Starting Member
4 Posts |
Posted - 2009-03-24 : 07:47:56
|
Thanks for that. Actually, this is a table that will be queried regularly, but space is not an issue. Can you give me a sample query syntax to calculate each age for each row please?quote:
Originally posted by darkdusky
You have 2 options 1. a calculated column in the table to calculate each age for each row. 2. use a VIEW to display the age for each row. Which you use should be decided by how often this will be used, and does table have a high proportion of INSERT / UPDATE or mostly SELECTs. Calculated column takes up space etc but can be queried quicker to select than view (although you could index the view), but they will slow inserts and updates on the table.
babsonoon |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-03-24 : 09:52:26
|
| Does it really make sense to store the age in a row in a table, since it can change from one day to the next?Why not just calculate the age at the time you need it, so that you know it is accurate and up to date.?You might take a look at the functions on the links below.This function returns age in format YYYY MM DD.Age Function F_AGE_YYYY_MM_DD:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729This function returns age in years.Age Function F_AGE_IN_YEARS:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462CODO ERGO SUM |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-03-24 : 10:13:49
|
| Using function f_age_in_years in a view:CREATE VIEW vwAgeASselect [Age] = dbo.F_AGE_IN_YEARS( dt, getdate()), IDfromTableNameGO |
|
|
|
|
|
|
|
|
Calculating Age for an entire table