September 21, 2010 at 2:33 am
Hi member's
my name Johan, please help me on this problem.
i've a table structured like this
table name : Personal
field : - PerID (identity)
- PerName (varchar (50))
- PerBirthDate (datetime)
- PerSex (char(1))
here's i want to achieve :
i'd like to display the age of each person here. I've tried using datediff and it's work.
The problem is how can i round an age of person into something like this
for example :
if the datediff result = 46 <-- i'd like to round it into 50
if the datediff result = 44 <-- i'd like to round it into 40
is there any built-in function in sql server to achieve this ?
or is there any sugestion for me ?
thx all.
regards.
September 21, 2010 at 3:07 am
Use CASE:
-- make some sample data to test the solution
DROP TABLE #Personal
CREATE TABLE #Personal (
PerID INT IDENTITY(1,1),
PerName varchar (50),
PerBirthDate datetime,
PerSex char(1))
INSERT INTO #Personal (PerName, PerBirthDate, PerSex)
SELECT 'A 1945-08-25', '1945-08-25 09:49:50.230', 'M' UNION ALL
SELECT 'B 1948-09-25', '1948-09-25 09:49:50.230', 'M' UNION ALL
SELECT 'C 1954-10-25', '1954-10-25 09:49:50.230', 'M' UNION ALL
SELECT 'D 1956-10-25', '1956-10-25 09:49:50.230', 'M' UNION ALL
SELECT 'E 1958-11-25', '1958-11-25 09:49:50.230', 'M' UNION ALL
SELECT 'F 1959-12-25', '1959-12-25 09:49:50.230', 'M' UNION ALL
SELECT 'G 1964-12-25', '1964-12-25 09:49:50.230', 'M' UNION ALL
SELECT 'H 1966-01-25', '1966-01-25 09:49:50.230', 'M' UNION ALL
SELECT 'I 1975-02-25', '1975-02-25 09:49:50.230', 'M'
-- solution
SELECT PerName, PerBirthDate, PerSex, [Age],
AgeBracket = CASE
WHEN [Age] > 65 THEN 70
WHEN [Age] > 60 THEN 60
WHEN [Age] > 55 THEN 60
WHEN [Age] > 50 THEN 50
WHEN [Age] > 45 THEN 50
WHEN [Age] > 40 THEN 40
WHEN [Age] > 35 THEN 40
WHEN [Age] > 30 THEN 30
ELSE NULL END -- expand your range at both ends
FROM (
SELECT PerName, PerBirthDate, PerSex,
-- choose an algorithm which works correctly, this doesn't
[Age] = DATEDIFF(yy, PerBirthDate, GETDATE())
FROM #Personal
) d
Here's a great article which explains the issues with using DATEDIFF to calculate age:
http://www.sqlservercentral.com/articles/T-SQL/63351/[/url]
Edited to add link to Lynn's article.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 21, 2010 at 3:24 am
thx for the respon, i'll try it.
would you mind if i ask more if there is unclear about this ?
September 21, 2010 at 3:30 am
cutecrazyguy (9/21/2010)
thx for the respon, i'll try it.would you mind if i ask more if there is unclear about this ?
Not at all Johan, if you're unsure about anything, just ask.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 21, 2010 at 3:54 am
thank you,,
i've tried it, and write some query like this
select PerName, PerBirthDate, CASE DateDiff(yy,PerBirthDate,GETDATE())
WHEN DateDiff(yy,PerBirthDate,GETDATE()) > 60 THEN 60
WHEN DateDiff(yy,PerBirthDate,GETDATE()) > 55 THEN 60
WHEN DateDiff(yy,PerBirthDate,GETDATE()) > 50 THEN 50
WHEN DateDiff(yy,PerBirthDate,GETDATE()) > 45 THEN 50
WHEN DateDiff(yy,PerBirthDate,GETDATE()) > 40 THEN 40
WHEN DateDiff(yy,PerBirthDate,GETDATE()) > 35 THEN 40
WHEN DateDiff(yy,PerBirthDate,GETDATE()) > 30 THEN 30
ELSE NULL END
FROM UDPersonnel
but got an error state Incorrect syntax near '>'.
please don't get bored, i'm a new in this sql and programming.
September 21, 2010 at 3:57 am
What about ROUND(DATEDIFF(yy, PerBirthDate, GETDATE()), -1)?
September 21, 2010 at 4:00 am
A small modification and your query will run:
select PerName, PerBirthDate,
CASE
WHEN DateDiff(yy,PerBirthDate,GETDATE()) > 60 THEN 60
WHEN DateDiff(yy,PerBirthDate,GETDATE()) > 55 THEN 60
WHEN DateDiff(yy,PerBirthDate,GETDATE()) > 50 THEN 50
WHEN DateDiff(yy,PerBirthDate,GETDATE()) > 45 THEN 50
WHEN DateDiff(yy,PerBirthDate,GETDATE()) > 40 THEN 40
WHEN DateDiff(yy,PerBirthDate,GETDATE()) > 35 THEN 40
WHEN DateDiff(yy,PerBirthDate,GETDATE()) > 30 THEN 30
ELSE NULL END
FROM UDPersonnel
However, it will not calculate the age correctly because datediff(yy... merely counts the number of year boundaries crossed between the two dates. The link in my post above describes this and how to get around it.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 21, 2010 at 4:14 am
i've read your link chris, but don't get the point.
your modification on the query helped me. thank's alot.
i'll as you later if there another unclear about this age.
thx all.
September 21, 2010 at 4:22 am
cutecrazyguy (9/21/2010)
i've read your link chris, but don't get the point.
No problem Johan, here's a modified query to return the correct age.
-- solution
DECLARE @TodayDate DATE
SET @TodayDate = GETDATE() -- today as a DATE-datatype
SELECT PerName, PerBirthDate, PerSex, [Age],
AgeBracket = CASE
WHEN [Age] > 65 THEN 70
WHEN [Age] > 60 THEN 60
WHEN [Age] > 55 THEN 60
WHEN [Age] > 50 THEN 50
WHEN [Age] > 45 THEN 50
WHEN [Age] > 40 THEN 40
WHEN [Age] > 35 THEN 40
WHEN [Age] > 30 THEN 30
ELSE NULL END -- expand your range at both ends
FROM (
SELECT PerName, PerBirthDate, PerSex,
[Age] = DATEDIFF(yy, PerBirthDate, @TodayDate) -
CASE WHEN @TodayDate < DATEADD(yy, DATEDIFF(yy, PerBirthDate, @TodayDate), PerBirthDate)
THEN 1 ELSE 0 END
FROM #Personal
) d
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 21, 2010 at 10:26 am
Thanks a lot.
🙂
September 21, 2010 at 8:37 pm
i'm sorry for disturbing again,
i've tried the Last modified query as you write on.
it works, but there some little question for me,
you put a 'd' word in the end of statement, what does it used for ?
is that something like aliasses ?
thx.
September 21, 2010 at 10:02 pm
cutecrazyguy (9/21/2010)
you put a 'd' word in the end of statement, what does it used for ?is that something like aliasses ?
thx.
Exactly, tat is the alias for the sub-query present in the FROM clause..
it is like:
SELECT <columns>
FROM
(
Sub -query
) Alias
That Alias is "d"
September 21, 2010 at 10:25 pm
You can just use the ROUND function to round the age up or down.
select
a.*,
RoundedAge = round(a.Age,-1)
from
( -- Test Data
select Age = 65 union all
select Age = 64 union all
select Age = 37 union all
select Age = 33 union all
select Age = 26
) a
Results:
Age RoundedAge
----------- -----------
65 70
64 60
37 40
33 30
26 30
(5 row(s) affected)
September 22, 2010 at 2:12 am
Michael Valentine Jones (9/21/2010)
You can just use the ROUND function to round the age up or down......
You can Michael, and there's a 50/50 chance it will work as-is here. Depends whether 55 is rounded up to 60 or down to 50, which hasn't yet been specified. Of course it wouldn't take much to fix that...
select
a.*,
RoundedAge1 = round(a.Age,-1),
RoundedAge2 = CAST(ROUND(a.Age-0.1, -1) AS INT)
from
( -- Test Data
select Age = 66 union all
select Age = 65 union all
select Age = 64 union all
select Age = 37 union all
select Age = 33 union all
select Age = 26
) a
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply