Age Banding (for a beginner, please!)

Question

Age Banding (for a beginner, please!)

simon.wilson 8043

Old Hand

Points: 364
More actions
October 5, 2009 at 6:59 am

#226850

Hi, my first post on here so 😀
I have done a number of SQL courses over the last decade or so, but never had the opportunity to get stuck into it. At last, my job is changing so that I can start using it - so I am soo looking forward to the 3-day SSRS course later this month.
But in the meantime, I am trying to create a report that counts the number of people in a particular age band. E.g. How many do we have in the 17-24 age range, etc.
As I said, I am only on the first rung of the SQL ladder, so any explanations would have to be quite simple, but even a pointer in the direction would be useful. My manager is much more experienced - but I'd like the chance to have a go, before asking for her help!
Thanks
Simon

Viewing 13 posts - 1 through 12 (of 12 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

It would be easier to supply an example if you posted some more information as is mentioned in article referenced the first link in my signature.

Are you writing the SQL query as well as the report?

I would do something like this in the query:

SELECT

COUNT(*) AS no_employees,

CASE

WHEN DATEDIFF(YEAR, BirthDate, GETDATE()) BETWEEN 17 AND 24 THEN '17-24'

WHEN DATEDIFF(YEAR, BirthDate, GETDATE()) BETWEEN 25 AND 34 THEN '25-34'

WHEN DATEDIFF(YEAR, BirthDate, GETDATE()) BETWEEN 35 AND 44 THEN '35-44'

ELSE '45 AND Older'

END AS age_group

FROM

HumanResources.Employee AS E

GROUP BY

CASE

WHEN DATEDIFF(YEAR, BirthDate, GETDATE()) BETWEEN 17 AND 24 THEN '17-24'

WHEN DATEDIFF(YEAR, BirthDate, GETDATE()) BETWEEN 25 AND 34 THEN '25-34'

WHEN DATEDIFF(YEAR, BirthDate, GETDATE()) BETWEEN 35 AND 44 THEN '35-44'

ELSE '45 AND Older'

END

ORDER BY

age_group

The CASE statement is the key piece. You could add a calculated column to the dataset in the report and do the similar thing.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

davidandrews13 SSCertifiable Points: 6820 More actions · Answer 2

it does fall down if your birthday is this year but hasn't happened yet

select DATEDIFF(year,'1981-12-04',getdate())

will say 28 when they will still be 27

admittedly, its probably safe to say that the following has its down falls as well but this is what i use.

select floor(datediff(day, '1981-12-04', GETDATE()) / 365.25)

simon.wilson 8043 Old Hand Points: 364 More actions · Answer 3

Thanks Jack.

I'll read the etiquette tonight when I get home (and get the kids to bed!).

I'll study your answer more closely tomorrow.

Cheers,

Simon

simon.wilson 8043 Old Hand Points: 364 More actions · Answer 4

Thanks David.

Having just read a few threads about age, I'm glad to say that it isnt a problem for me as the database I get my data from already works the age out itself! It's also made a little easier in that the report only has to be run once a year, as at 31st March (for a fiscal year end).

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

davidandrews13 (10/5/2009)
it does fall down if your birthday is this year but hasn't happened yet
select DATEDIFF(year,'1981-12-04',getdate())
will say 28 when they will still be 27
admittedly, its probably safe to say that the following has its down falls as well but this is what i use.
select floor(datediff(day, '1981-12-04', GETDATE()) / 365.25)

Yeah, I probably could have/should have explained that, but I was trying to keep it simple and it is also why I said that's where I'd start. There are several ways to get it "right", but I felt that they were a little complex to try to explain for a beginner and since the manager was mentioned as being more skilled, I thought I'd just give a starting point.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

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

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

Age Function F_AGE_IN_YEARS

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 7

Proper calculation of age should never include /365.35. It also doesn't need to be complex...

DECLARE @DOB DATETIME

SET @DOB = '2008-03-01'

DECLARE @Now DATETIME

SET @Now = '2009-03-01'

--===== This is the proper way to do it for years

SELECT CASE

WHEN DATEADD(yy, DATEDIFF(yy, @DOB, @Now) , @DOB) > @Now

THEN DATEDIFF(yy, @DOB, @Now) - 1

ELSE DATEDIFF(yy, @DOB, @Now)

END

You could easily sub GETDATE() for @Now in the SELECT.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kevlray SSCommitted Points: 1908 More actions · Answer 8

I found a good age calculator, but it is written in Crystal Reports syntax. But the concept is that you have compare the year, then the month, then the day. Not sure how to write in SQL.

PatientVistProcs.DateofServiceFrom is the date I am comparing to (i.e., current date?), PatientProfile.Birthdate is client's Date of Birth.

// Convert Date of Birth to Age in Years

// Get the number of years

// Correct value only if birthday has already occurred

// this year

numberVar ageyrs := Year ({PatientVisitProcs.DateOfServiceFrom}) - Year({PatientProfile.Birthdate});

// Determine if month has already passed

numberVar agemo := Month ({PatientProfile.Birthdate}) - Month({PatientVisitProcs.DateOfServiceFrom});

// Determine if day in month has already passed

numberVar ageday := Day ({PatientProfile.Birthdate}) - Day({PatientVisitProcs.DateOfServiceFrom});

// Subtract a year if birthday has not occurred yet

// this year

If agemo > 0 OR ( (agemo = 0) and (ageday > 0) )

then ageyrs := ageyrs -1

else ageyrs := Truncate(ageyrs);

ageyrs := Truncate(ageyrs);

ageyrs

Jack Corbett SSC Guru Points: 184394 More actions · Answer 9

Jeff Moden (10/5/2009)
Proper calculation of age should never include /365.35. It also doesn't need to be complex...
DECLARE @DOB DATETIME
SET @DOB = '2008-03-01'
DECLARE @Now DATETIME
SET @Now = '2009-03-01'
--===== This is the proper way to do it for years
SELECT CASE
WHEN DATEADD(yy, DATEDIFF(yy, @DOB, @Now) , @DOB) > @Now
THEN DATEDIFF(yy, @DOB, @Now) - 1
ELSE DATEDIFF(yy, @DOB, @Now)
END
You could easily sub GETDATE() for @Now in the SELECT.

As usual a nice solution Jeff. I need to think out of box or maybe get in the box.:w00t: One or the other anyway.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 10

Jack Corbett (10/7/2009)
Jeff Moden (10/5/2009)
Proper calculation of age should never include /365.35. It also doesn't need to be complex...
DECLARE @DOB DATETIME
SET @DOB = '2008-03-01'
DECLARE @Now DATETIME
SET @Now = '2009-03-01'
--===== This is the proper way to do it for years
SELECT CASE
WHEN DATEADD(yy, DATEDIFF(yy, @DOB, @Now) , @DOB) > @Now
THEN DATEDIFF(yy, @DOB, @Now) - 1
ELSE DATEDIFF(yy, @DOB, @Now)
END
You could easily sub GETDATE() for @Now in the SELECT.
As usual a nice solution Jeff. I need to think out of box or maybe get in the box.:w00t: One or the other anyway.

Heh... sometimes, I don't realize I'm in a box. 😛

I can't take the credit for this one. I first saw it on a post by Dave Ballantyne and he had gotten it from someplace else. I'm just quick to apply new lessons I've learned so the box doesn't seem so big. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sing4you Hall of Fame Points: 3034 More actions · Answer 11

Jeff, that solution for Age in Years is sweet. (I have saved it for future use.)

Thanks 😀

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 12

Aye. Thank you for the feedback. I just wish I was the one that thought of it. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)