Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

How can we calculate Age of employee? Expand / Collapse
Author
Message
Posted Thursday, January 24, 2013 3:34 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 21,209, Visits: 14,894
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1411371
Posted Thursday, January 24, 2013 4:08 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:15 PM
Points: 23,000, Visits: 31,488
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1411388
Posted Friday, January 25, 2013 11:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:53 PM
Points: 1,072, Visits: 6,342
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

day________dob________age
1992-07-07 1927-04-24 65
2000-06-06 1915-01-31 85
2003-12-15 1923-06-22 80
2002-03-22 1917-01-24 85
2008-02-28 2007-03-01 0



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




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
Post #1411853
Posted Saturday, January 26, 2013 2:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 3, 2014 12:25 AM
Points: 73, Visits: 158
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
Post #1411995
Posted Saturday, January 26, 2013 1:26 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
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."
Post #1412072
Posted Thursday, February 14, 2013 9:48 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 20, 2014 5:23 PM
Points: 4,576, Visits: 8,341
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.
Post #1420371
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse