Is it possible ?:) - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Is it possible ?:)

Author

Topic

es111
Starting Member

3 Posts

Posted - 2009-09-21 : 05:16:44

Hi,
As i'm a beginner with Sql a have a problem that i need a help with :

In my Table Tickets I've got a column with date of born - would it be possible to group that column by age into few groups - I mean for example - people that are in age between 0-9, 10-16, 17-24 etc..

I wonder -and still don't know- if it's possible to group it like this- if it is but it's too complicated let's say i can add a new column just iwth year of born and age could be achived as current year-year of born.

i hope i make it clear:)
i'll be thankful for any kind of help!

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-09-21 : 06:03:28

If You include age column, will be quite easy! but you will keep on updating the age column.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

es111
Starting Member

3 Posts

Posted - 2009-09-21 : 06:26:34

quote:
Originally posted by senthil_nagore

If You include age column, will be quite easy! but you will keep on updating the age column.

yep, i know :);/

Andreas
Starting Member

11 Posts

Posted - 2009-09-21 : 08:44:46

It's not as hard as it might seem, you just have to join on a table describing the age groups, here's an example:

declare @Tickets table(Born smalldatetime)

insert into @Tickets(Born) values('1978-12-11')
insert into @Tickets(Born) values('1982-02-15')
insert into @Tickets(Born) values('1989-10-02')
insert into @Tickets(Born) values('1985-07-17')
insert into @Tickets(Born) values('2000-01-15')
insert into @Tickets(Born) values('1965-11-30')

declare @AgeGroup table(YearFrom int, YearTo int)

insert into @AgeGroup values(0, 9)
insert into @AgeGroup values(10, 19)
insert into @AgeGroup values(20, 29)
insert into @AgeGroup values(30, 39)
insert into @AgeGroup values(40, 49)
insert into @AgeGroup values(50, 59)
insert into @AgeGroup values(60, 200)

select
   T.Born,
   cast(A.YearFrom as varchar(3)) + '-' + cast(A.YearTo as varchar(2)) AgeGroup
from
   @Tickets T
   inner join @AgeGroup A
      on datediff(year, T.Born, getdate()) between A.YearFrom and A.YearTo

Re the issue of an age column, adding that and keeping it updated would be a denormalization, so it's not a good idea. However, you could use a computed column for the sake of convenience:

declare @Tickets table(Born smalldatetime, Age as datediff(year, Born, getdate()))

es111
Starting Member

3 Posts

Posted - 2009-09-21 : 11:10:03

Thanks Anderas for your help!, thoug i got some other solution - with Select Case and it look's like it's working ok,

thanks once again !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources