Age Bounds 2

[xxing]

Hi I have the following data which i have scripted for your convenience:

if object_id('tempdb..#people') is not null drop table #people
create table #people
(
DOB datetime
)

insert into #people values('19630901')
insert into #people values('19640901')
insert into #people values('19650901')
insert into #people values('19660901')
insert into #people values('19670901')
insert into #people values('19681001')
insert into #people values('20000901')

I have to get age bands, Which is to the nearest 5

Age Agebands
48 45-50
47 45-50
46 45-50
44 40-45
43 40-45
11 10-15

The age is calculated as year(getdate()) - year(DOB)
How do I get the age bands?

regards
Mark

 

[bborissov]

DECLARE @people table (DOB datetime)

DECLARE @testDate datetime
SET @testDate = '19630901'
WHILE YEAR(@testDate) < YEAR(GETDATE())
   BEGIN
      insert into @people values(@testDate)
      SET @testDate = DATEADD(yy, 1, @testDate)
   END


SELECT Age,
       CAST(ROUND(Age/5,0)*5 AS varchar(2))+ ' - '+
       CAST(ROUND((Age+5)/5,0)*5 as varchar(2))     AS Agebands
FROM (SELECT YEAR(GETDATE()) - YEAR(DOB) AS Age
             FROM @People)  People

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[drlex]

Minor niggle on age - those people with birthdays still to come in this year will show as older than they are.
Perhaps use DATEDIFF(dd,DOB,GETDATE())/365.25 for a better calculation of age?

soi là, soi carré