January 23, 2013 at 1:33 pm
r_slot (1/23/2013)
The following combinations give a negative result
What are you using?
_____________
Code for TallyGenerator
January 24, 2013 at 6:42 am
SQL Server 2012
January 24, 2013 at 7:12 am
r_slot (1/24/2013)
SQL Server 2012
What I think you were asked is what formula you are using to compute age, not what version of SQL Server.
January 24, 2013 at 3:10 pm
I am using your code of course.
January 24, 2013 at 3:20 pm
r_slot (1/24/2013)
I am using your code of course.
I haven't posted it yet! :w00t:
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 24, 2013 at 3:34 pm
r_slot (1/24/2013)
I am using your code of course.
Which version of his code?
What are your errors?
What is the data that you are using for testing the code?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 24, 2013 at 4:08 pm
r_slot (1/24/2013)
I am using your code of course.
Good. Now, if you would please post the code you are are using, not just refer me to my old code, we can see what may be happening.
January 25, 2013 at 11:03 am
Lynn Pettis (1/24/2013)
r_slot (1/24/2013)
I am using your code of course.Good. Now, if you would please post the code you are are using, not just refer me to my old code, we can see what may be happening.
It looks fine to me. Here's your code Lynn, tweaked only to read table values - the "errant" ones posted earlier:
SELECT
[day],
dob,
age = datediff(yy,dob,[day]) -
case when [day] < dateadd(yy,datediff(yy,dob,[day]), dob) then 1 else 0 end
FROM (
SELECT [day] = '1992-07-07', dob = '1927-04-24' UNION ALL -- 65
SELECT [day] = '2000-06-06', dob = '1915-01-31' UNION ALL -- 85
SELECT [day] = '2003-12-15', dob = '1923-06-22' UNION ALL -- 80
SELECT [day] = '2002-03-22', dob = '1917-01-24' UNION ALL -- 85
SELECT [day] = '2008-02-28', dob = '2007-03-01' -- 0
) d
Results
[font="Courier New"]
day________dob________age
1992-07-071927-04-2465
2000-06-061915-01-3185
2003-12-151923-06-2280
2002-03-221917-01-2485
2008-02-282007-03-010
[/font]
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 26, 2013 at 2:50 am
Sorry for the small misunderstanding but I am using john.arnotts code. The results I mentioned before follow from the following code:
USE [SomeDatabase]
GO
/****** Object: UserDefinedFunction [dbo].[Age2] Script Date: 26-1-2013 10:41:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Robert
-- Create date: 23-01-2013
-- Description:Calculating Age
-- this function is not always OK
-- =============================================
ALTER FUNCTION [dbo].[Age2]
(
-- Add the parameters for the function here
@Birthday As datetime,
@Day_of_Decease As datetime
)
RETURNS nvarchar(100)
AS
BEGIN
-- Declare the variables here
Declare @Birth Datetime
,@Death Datetime
,@Today Datetime
,@AgeYears int
,@AgePlusMonths int
,@AgePlusDays int
,@LastBD datetime
,@LastMonthBD datetime
,@Result nvarchar(100)
--======================--
SET @Birth = @Birthday
SET @Death = @Day_of_Decease
SET @Today = Getdate()
--======================--
-- first part: athlete is still alive
IF @Death Is NULL
BEGIN
SET @AgeYears = year(@today)-year(@Birth)
- case when month(@today) < month(@Birth)
or (month(@today) = month(@Birth) and day(@today) < day(@Birth))
then 1
else 0
end
SET @LastBD = dateadd(year,@AgeYears,@Birth)
SET @AgePlusMonths = datediff(month, @LastBD, @Today)
- case when month(@today) <= month(@LastBD) and day(@today) < day(@LastBD)
then 1
else 0
end
- case when month(@Birth) = 2 and day(@Birth) = 29
then 1
else 0
end
SET @LastMonthBD = dateadd(month,@AgePlusMonths,@LastBD)
SET @AgePlusDays = datediff(day, @LastMonthBD, @Today)
END
-- second part: the athlete is deceased
ELSE
BEGIN
SET @AgeYears = year(@Death)-year(@Birth)
- case when month(@Death) < month(@Birth)
or (month(@Death) = month(@Birth) and day(@Death) < day(@Birth))
then 1
else 0
end
SET @LastBD = dateadd(year,@AgeYears,@Birth)
SET @AgePlusMonths = datediff(month, @LastBD, @Death)
- case when month(@Death) < month(@LastBD)
or (month(@Death) = month(@LastBD) and day(@Death) < day(@LastBD))
then 1
else 0
end
- case when month(@Birth) = 2 and day(@Birth) = 29
then 1
else 0
end
SET @LastMonthBD = dateadd(month,@AgePlusMonths,@LastBD)
SET @AgePlusDays = datediff(day, @LastMonthBD, @Death)
END
-- third part: Return the result of the function
SET @Result = CAST(@AgeYears As nvarchar(20)) + 'yr ' +
CAST(@AgePlusMonths As nvarchar(20)) + 'mnth '+ CAST(@AgePlusDays As nvarchar(20)) + 'days'
RETURN @Result
END
I hope this gives a better insight.
Grz,
Robert
January 26, 2013 at 1:26 pm
Looks to me as though whenever "today's" date has a month later in the year than the birthday and a day-of-month smaller than the day part of the birthday, the algorithm will end up with a negative number of days. The result is mathematically correct, but could be adjusted for readability.
For instance, with DOB=19270424 and Today=19920707, we would expect an age of 65 years and this code gives us that. It then reports that the age is 17 days less than 3 months more than the 65 years. You may want to examine the steps it took and adjust them to show 2 months and 13 days.
----
Edit: Corrected typo from "... more than the 5 years." to "... more than the 65 years."
February 14, 2013 at 9:48 pm
Should be really way simpler
SELECT
dob,
[day],
DATEDIFF(yy, 0, [day] - dob) Years,
DATEDIFF(MM, 0, [day] - dob)%12 Months,
DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, 0, [day] - dob), 0), [day] - dob) days
FROM (
SELECT [day] = CONVERT(datetime, '1992-07-07', 120), dob = CONVERT(datetime, '1927-04-24', 120) UNION ALL -- 65
SELECT [day] = '2000-06-06', dob = '1915-01-31' UNION ALL -- 85
SELECT [day] = '2003-12-15', dob = '1923-06-22' UNION ALL -- 80
SELECT [day] = '2002-03-22', dob = '1917-01-24' UNION ALL -- 85
SELECT [day] = '2008-02-28', dob = '2007-03-01' -- 0
) d
or even more simple:
SELECT
dob,
[day],
YEAR([day] - dob)-1900 Years,
MONTH([day] - dob)-1 Months,
DAY([day] - dob)-1 days
FROM (
SELECT [day] = CONVERT(datetime, '1992-07-07', 120), dob = CONVERT(datetime, '1927-04-24', 120) UNION ALL -- 65
SELECT [day] = '2000-06-06', dob = '1915-01-31' UNION ALL -- 85
SELECT [day] = '2003-12-15', dob = '1923-06-22' UNION ALL -- 80
SELECT [day] = '2002-03-22', dob = '1917-01-24' UNION ALL -- 85
SELECT [day] = '2008-02-28', dob = '2007-03-01' -- 0
) d
([day] - dob) gives you the age in seconds (milliseconds if you wish), and then you simply figure out YEAR, MONTH and DAY of that value.
_____________
Code for TallyGenerator
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply