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 |
|
arvidb
Starting Member
7 Posts |
 Posted - 2010-06-22 : 08:41:12
|
| As a beginner to databases and SQL Server I need your help. Assume the two following tables:Authors{ AuthorId int, [PK] FirstName nvarchar(50) LastName nvarchar(50) DateOfBirth datetime . . .}Articles{ ArticelId int, [PK] AuthorId int, [FK] Date datetime Age float . . .}I want the Age column (the authors age when article was published) in table Articles to be calculatedas the difference between the Articles.Date and the Authors.DateOfBirth, for examlpe as datediff(day, Articles.Date, Authors.DateOfBirth)/365.25I have tried using Age as a computed column, but since I need to get DateOfBirth from the Authors table I can not get things to work.What would be the "best" solution to this matter?/arvidb |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-22 : 09:01:57
|
| you can use a join on AuthorId for tables Authors and Articles and then use datediff(dd,Date,DateOfBirth).Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
 |
|
|
arvidb
Starting Member
7 Posts |
Posted - 2010-06-22 : 09:24:56
|
quote:
Originally posted by Idera
you can use a join on AuthorId for tables Authors and Articles and then use datediff(dd,Date,DateOfBirth).Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH
Do you mean a join could be used in the computed column formula?/arvidb |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-22 : 09:47:41
|
| No you cannot use join for computed columns.The best you can do is create a function that can calulate the age based on the paramerts passed with the join.Something like thisALTER TABLE YourTable add AgeColumn AS dbo.udf_CalculateAge(AuthorId,Date)Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
|
|
|
|
|
|
|
|
Computed column Age?