| Author |
 Topic  |
|
|
rbpd5015
Starting Member
USA
4 Posts |
 Posted - 06/07/2012 : 10:00:42
|
Ok I am moving from Excel to SQL LOL.....
I could really use some help.
I just imported my entire Business Employee Database into SQL from Excel.
I have two fields that are calculated and that is AGE and Class years.
These are calculated based on DOB and Date Date of Class.
Now since I am moving to SQL those numbers will be static and never change so what must I do to make them calculate again? How does SQL handle this?
Thanks,
Matt |
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/07/2012 : 10:05:21
|
Have they been imported as populated columns?
You would have to drop them then add a calculated column
mycol as mycol1 - mycol2
Also check your dates and datatypes as sometimes they cause problems.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
Edited by - nigelrivett on 06/07/2012 10:06:05 |
 |
|
|
rbpd5015
Starting Member
USA
4 Posts |
Posted - 06/07/2012 : 14:12:44
|
Ok I am in the 2008 studio manager
I have a column called
emp_DOB
and below it
emp_Age
I am in the age column now and clicked
COMPUTED COLUMN SPECIFICATION
below it is formula.
What formula can I put in to calculate the age of the Employee?
Matt |
|
|
|
rbpd5015
Starting Member
USA
4 Posts |
Posted - 06/07/2012 : 14:25:59
|
I was told to use this
floor(datediff(day, [DateOfBirth], getdate())/(365.25))
but it doesnt seem to work correct |
|
|
|
Ida Hoe
Starting Member
USA
3 Posts |
Posted - 06/07/2012 : 14:32:03
|
quote:
Originally posted by rbpd5015
Ok I am moving from Excel to SQL LOL.....
I could really use some help.
I just imported my entire Business Employee Database into SQL from Excel.
I have two fields that are calculated and that is AGE and Class years.
These are calculated based on DOB and Date Date of Class.
Now since I am moving to SQL those numbers will be static and never change so what must I do to make them calculate again? How does SQL handle this?
Thanks,
Matt
If I were running reports using sql query, I would not bother to store the age, I would recalculate every time I chose the data.
SELECT DOB
,GETDATE()as today
,DATEDIFF(yy,moddate,GETDATE())-
CASE
when DATEADD(yy,datediff(yy,moddate,getdate()),moddate) > GETDATE() then 1
else 0
end as AGE
FROM MyNewTable;
Or use this same logic to UPDATE AGE periodically, or create a procedure ....
|
|
|
|
rbpd5015
Starting Member
USA
4 Posts |
Posted - 06/07/2012 : 14:41:11
|
| I have it setup to display as a gridview on a webpage. So I better off just having the age calculated each time the PAGE is loaded instead of worrying about creating a calculated field? |
|
|
|
Ida Hoe
Starting Member
USA
3 Posts |
Posted - 06/07/2012 : 14:47:00
|
Well, that is the way I would do it - then it's always up to the minute just like a computed column - Either way works. The calculation is the same.
|
Edited by - Ida Hoe on 06/07/2012 15:52:04 |
|
|
| |
Topic |
|
Question on calculated Fields
