Range query by age help. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Range query by age help.

Author

Topic

comn8u
Starting Member

9 Posts

Posted - 2007-01-18 : 18:15:01

I would like to generate a report based on age from an existing table. All I have is birthdate column to subtract from getdate(). I would like to create age bracket columns. The example below shows that there is 150 people aged 0-4 years old, 75 people aged 5-9, 225 people aged 10-14, etc. Would I use a case statement? Would I use the COALESCE function? I can come up with the calculations and the columns, I just can't fuse the values of my made up columns to result in one row. I need help, please....

here's a sample of what I want the query output to look like:
0-4 5-9 10-14 15-17
----------------------
150 75 225 35

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-18 : 18:16:03

Why don't you just pass the raw data back to your application and have your application handle this?

Tara Kizer

comn8u
Starting Member

9 Posts

Posted - 2007-01-18 : 18:46:24

I'm trying to output data from SQL for demonstration. I will probably build an application to handle it. I just wanted to see if there was a way to output it using plain SQL. I thought I'd find a guru on here that would provide quick syntax.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-18 : 19:19:07

Computing the age of someone is more difficult than it might seem when you take into account different month lengths, leap year, and other things. Function F_AGE_IN_YEARS from the following link returns age in years:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

Here is a sample query using the F_AGE_IN_YEARS function that gives output like you showed:


select
	[0-4] = sum(case when Age between 0 and 4 then 1 else 0 end) ,
	[5-9] = sum(case when Age between 5 and 9 then 1 else 0 end) ,
	[10-14] = sum(case when Age between 10 and 14 then 1 else 0 end) ,
	[15-17] = sum(case when Age between 15 and 17 then 1 else 0 end)
from
	(
	select 
		[Age] = dbo.F_AGE_IN_YEARS( aa.START_DATE, getdate())
	
	from
		(
		-- test data
		select	START_DATE =convert(datetime,'20040407') union all
		select '20000407' union all
		select '19951011'  union all
		select '19920101'  union all
		select '19910101'
		) aa
	) a

Results:


0-4         5-9         10-14       15-17       
----------- ----------- ----------- ----------- 
1           1           1           2

(1 row(s) affected)

CODO ERGO SUM

comn8u
Starting Member

9 Posts

Posted - 2007-01-18 : 19:58:38

See, I knew there would be a genius around here. Thank you very much. That's exactly what I need.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources