Deriving age as at birthdate and then every year till current

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

Function F_AGE_IN_YEARS in the script on the link below calculates age in years from @START_DATE through @END_DATE and returns the age as an integer.

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 2

Is there anyway other than using this function of doing it. I dont really want to use a function on the query If I dont have too.

Hunterwood SSCrazy Points: 2074 More actions · Answer 3

Use a tally-table:

Create Table #Dim_Child

(DW_Child_ID int, DOB DateTime)

INSERT INTO #Dim_child

SELECT 1 AS DW_Child_ID, '1993-03-13 00:00:00.000'AS DOB

UNION

SELECT 2 AS DW_Child_ID,'1988-05-11 00:00:00.000' AS DOB

UNION

SELECT 3 AS DW_Child_ID, '2000-07-30 00:00:00.000' AS DOB

--===== Create and populate the Tally table on the fly

SELECT TOP 200

IDENTITY(INT,0,1) AS N

INTO #Tally

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

select DW_Child_ID, DOB, N AS Age

from #Dim_child

cross join #Tally

where YEAR(DATEADD(year, N, DOB)) <= YEAR(getdate())

/Markus

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 4

Brilliant. I added the YEAR into the SELECT Bit so I can use it later on and it works a treat.

Thanks

Debbie