Question on calculated Fields - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Question on calculated Fields

Author

Topic

rbpd5015
Starting Member

4 Posts

Posted - 2012-06-07 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-07 : 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.

rbpd5015
Starting Member

4 Posts

Posted - 2012-06-07 : 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

4 Posts

Posted - 2012-06-07 : 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

3 Posts

Posted - 2012-06-07 : 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

4 Posts

Posted - 2012-06-07 : 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

3 Posts

Posted - 2012-06-07 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources