?
Solved

In SQL Calculate Date of Birth based on Year

Posted on 2014-08-26
11
Medium Priority
?
128 Views
Last Modified: 2015-01-02
I need to calculate the age of the person based on the how old they were on January 1 of the corresponding YEAR.
 
My SQL results look something like the following:
ID           Year           birthdate
1             2014          1948-04-11 00:00:00.000
2             2012          1960-01-10 00:00:00.000
3             2013          1954-09-28 00:00:00.000

Open in new window


After I have determined the age, I then need to place that person into one of five category age ranges:
(1-17, 18-34, 35-54, 55-74, 75 and up)

Ideally, the results would look something like this:
ID           Year           birthdate                                          Age             Age Group                              
1             2014          1948-04-11 00:00:00.000               65               55-74
2             2012          1960-01-10 00:00:00.000               51               35-54
3             2013          1954-09-28 00:00:00.000               58                55-74

Open in new window

0
Comment
Question by:swaggrK
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 40286408
How about using this function:
CREATE function [dbo].[fn_Get_Age]
(@i_BirthDate AS datetime)

RETURNS INT 
AS

BEGIN
declare @finalAge int = 0
declare @DOB datetime              
Set  @DOB = @i_BirthDate

 
Select @finalAge = 
case
      when DATEADD(YY, DATEDIFF(YY, @DOB , GETDATE()),  @DOB )  > GETDATE()
      then DATEDIFF(YY, @DOB , GETDATE()) - 1
      else DATEDIFF(YY, @DOB , GETDATE())
end 

return @finalAge 
END

Open in new window

0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 40286421
See if you can get that working, then let's put the last column, Age Group together.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40286448
SELECT ID, Year, Age, CASE
    WHEN Age >= 75 THEN '75+'
    WHEN Age >= 55 THEN '55-74'
    WHEN Age >= 35 THEN '35-54'
    WHEN Age >= 18 THEN '18-34'
    ELSE '1-17' END AS [Age Group]
FROM ...
CROSS APPLY (
    SELECT DATEDIFF(YEAR, birthdate, CAST(Year AS char(4)) + '0101') -
        CASE WHEN DATEPART(DAYOFYEAR, birthdate) = 1 THEN 0 ELSE 1 END AS Age
) AS assign_alias
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 15

Expert Comment

by:David L. Hansen
ID: 40286451
Well done Scott!
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40286458
For example:


SELECT ID, Year, Age, CASE
    WHEN Age >= 75 THEN '75+'
    WHEN Age >= 55 THEN '55-74'
    WHEN Age >= 35 THEN '35-54'
    WHEN Age >= 18 THEN '18-34'
    ELSE '1-17' END AS [Age Group]
FROM (
    SELECT 1 AS ID,      2014 AS Year, '1948-04-11 00:00:00.000' AS birthdate UNION ALL
    SELECT 2,            2012,         '1960-01-10 00:00:00.000' UNION ALL
    SELECT 3,            2013,         '1954-09-28 00:00:00.000' UNION ALL
    SELECT 4,            2011,         '2000-12-31 00:00:00.000' UNION ALL
    SELECT 4,            2011,         '2001-01-01 00:00:00.000'
) AS test_data
CROSS APPLY (
    SELECT DATEDIFF(YEAR, birthdate, CAST(Year AS char(4)) + '0101') -
         CASE WHEN DATEPART(DAYOFYEAR, birthdate) = 1 THEN 0 ELSE 1 END AS Age
) AS assign_alias
0
 

Author Comment

by:swaggrK
ID: 40286479
The following is my actual query. I am getting an Invalid column name 'Year' for
CAST(Year AS char(4))

 	SELECT A.AEA_EmployerOrganizationIdName [Employer], A.AEA_titleseason [Title/Season], workweek as [Work Weeks], 
	YEAR(startdate) as [Year], Region,[Primary Duty], [GROUP],[Contract Type], [Category Name], ISNULL(A.aea_BusinessRepIdName,'Unassigned') [Rep], [Show Name], 
	aea_actorcontractid [Contract ID], aea_partsandincrementsid [Part ID],
	ISNULL(FC.aea_ethniccodename,'Unknown') [Ethnicity], fc.birthdate,
	Age, CASE
     WHEN Age >= 75 THEN '75+'
     WHEN Age >= 55 THEN '55-74'
     WHEN Age >= 35 THEN '35-54'
     WHEN Age >= 18 THEN '18-34'
     ELSE '1-17' END AS [Age Group]
     
	from bpt_aea_workweektemp_actorid 
	INNER JOIN AEA_employeragreement as A 
	ON bpt_aea_workweektemp_actorid.aea_employeragreementid = A.AEA_employeragreementId
	INNER JOIN [FilteredContact] fc
	ON fc.contactid = bpt_aea_workweektemp_actorid.[Actor id]
	CROSS APPLY (
     SELECT DATEDIFF(YEAR, birthdate, CAST(Year AS char(4)) + '0101') AS Age
 ) AS assign_alias 

Open in new window

0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 40286486
You may need to wrap the word "Year" in brackets...like this: [Year]
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40286492
Sorry, I didn't realize "year" was not the actual column name.  Since you're deriving the name from:

YEAR(startdate) as [Year]

change the CROSS APPLY to:

CROSS APPLY (
     SELECT DATEDIFF(YEAR, birthdate, CAST(YEAR(startdate) AS char(4)) + '0101') -
          CASE WHEN DATEPART(DAYOFYEAR, birthdate) = 1 THEN 0 ELSE 1 END AS Age
 ) AS assign_alias
0
 

Author Comment

by:swaggrK
ID: 40286503
Thanks that worked!!!
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40286509
You're welcome!

CROSS APPLY is really good for assigning alias names you can actually use anywhere in the rest of the query: SELECT list, WHERE clause, ORDER BY, etc..
0
 
LVL 2

Expert Comment

by:Pratik Makwana
ID: 40363706
Here is your solution...........

DECLARE @dob  datetime
Declare @Grp varchar(100)
SET @dob='1948-04-11 00:00:00.000'


SELECT CONVERT(int,ROUND(DATEDIFF(hour,@dob,GETDATE())/8766.0,0))-1 AS Age
    ,      CASE WHEN dateadd(year, datediff (year, @dob, getdate()), @dob) > 55 or dateadd(year, datediff (year, @dob, getdate()), @dob) < 70
                        THEN
                        '55-74'
            ELSE '0-0'
       END AS [Age Group]
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question