T-SQL to get the counts of records within age ranges

This post is written just in case future me has to do this again…

I was emailed a spread sheet containing thousands of birth dates by my boss, and asked to return a segregation based on the number of them less than 20 years old, between 20 and 30, and so on… (The dates were, of course, strings in a weird format, but handling that is outside of the scope of this post.)

What I did was, import the dates into a table in SQL Server, which for want of better names, I called DOB, with a single field in it called DateOfBirth. I found an almost working solution on that god-forsaken portal of yesteryear, experts-exchange, of all places.

This query is based on the solution there, except it works properly.

select result.Age, count(*) Count
from 
(
select
  case when age < 20 then 'less than 20'
           when age between 20 and 30 then '20-30'
           when age between 31 and 40 then '31-40'
           when age between 41 and 50 then '41-50'
           when age between 51 and 300 then '51+'
/* and so on */
  end Age
from (SELECT DATEDIFF(year,DateOfBirth, getdate()) Age 
    from DOB where DateOfBirth is not null) age
) result
group by result.Age
order by Age

Advertisements

About Jerome

I am a senior C# developer in Johannesburg, South Africa. I am also a recovering addict, who spent nearly eight years using methamphetamine. I write on my recovery blog about my lessons learned and sometimes give advice to others who have made similar mistakes, often from my viewpoint as an atheist, and I also write some C# programming articles on my programming blog.
This entry was posted in Database and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s