SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Age calculation via date of birth, date of retirem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

irumsaba
Starting Member

1 Posts

Posted - 07/07/2011 :  03:59:14  Show Profile  Reply with Quote
let it be an employee mgmt system
i have
EID (PK)
Date of birth (datetime)
date of joining (datetime)
date of retirement(datetime)
date of death (date time)

i do not have to calculate age till today but i want to calculate age till date of retirement or date of death(if available)
i want to declare a new column "age" and keep the records of every employee in it
This query is working for it
<pre lang="sql">select case when not death is null then
datediff(year, birth, death )
else datediff(year , birth, date_of_retirement ) end from Table_3</pre>
it works ok, but i want to save the results in "age"

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/07/2011 :  04:20:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
See http://weblogs.sqlteam.com/peterl/archive/2009/02/13/Improved-anniversary-calculation-better-datediff.aspx


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

lappin
Posting Yak Master

182 Posts

Posted - 08/05/2011 :  04:19:25  Show Profile  Reply with Quote
You can put your query into a calculated column in the table, or create a VIEW based on the source table plus have an additional column using your calculation
Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 08/07/2011 :  12:22:17  Show Profile  Visit jcelko's Homepage  Reply with Quote
Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names, which you do not know.

Please tell us if you can change the DDL. T-SQL has a DATE data type now, so use it.

CREATE TABLE Personnel_Lifetimes
(emp_id CHAR(10) NOT NULL PRIMARY KEY,
birth_date DATE NOT NULL,
hire_date DATE NOT NULL,
CHECK (birth_date < hire_date),
retirement_date DATE, --- null means still working
CHECK (hire_date < retirement_date),
death_date DATE, --- null means still breathing
CHECK (hire_date < death_date)
);

>> I do not have to calculate age till today but I want to calculate age till date of retirement or date of death(if available) <<

>> I want to declare a new column "age" and keep the records of every employee in it <<

NO, you want to put the computation into a computed column or a VIEW. This is SQL and not punch card data processing. We do not materialize constantly changing data.

CREATE VIEW Personnel_Lifetime_Computations (..)
AS
SELECT emp_id, birth_date, hire_date, retirement_date, death_date,
DATEDIFF(YEAR, birth_date,
CASE WHEN CAST (CURRENT_TIMESTAMP AS DATE) < death_date
THEN CAST (CURRENT_TIMESTAMP AS DATE)
ELSE death_date END
AS emp_age,
DATEDIFF(YEAR, birth_date, retirement_date)
AS retirement_age,
DATEDIFF(YEAR, birth_date, death_date)
AS death_age
FROM Personnel_Lifetimes;

See how the NULLs work?



--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Go to Top of Page

jaehakp
Starting Member

USA
1 Posts

Posted - 10/21/2011 :  20:35:19  Show Profile  Reply with Quote
declare @BIRTHDATE date ='1/2/2000'
SELECT @BIRTHDATE,
DATEDIFF (year, @BIRTHDATE, getdate ())
- CASE
WHEN datepart (month, @BIRTHDATE) - datepart (month, getdate ()) <
0
THEN
0
WHEN datepart (month, @BIRTHDATE) - datepart (month, getdate ()) =
0
THEN
CASE
WHEN datepart (day, @BIRTHDATE) - datepart (day, getdate ()) <=
0
THEN
0
ELSE
1
END
ELSE
1
END
AS age
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000