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