Adding an if statment to an SQL query

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)

Adding an if statment to an SQL query

Author

Topic

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2009-01-02 : 13:05:18

I'm not really that hot when it comes to SQL, so I'm not even sure what I'm asking can be done!

I have a simple table of Gymnasts, where one of the fields is DOB (Date of Birth).

I need to create a view using the DOB only - I need to work out what age group the girls would fall into.

To do this, I first need to work out their age in December 31 of the current year, then based on that age (their year of birth - the current year), and then:

if < 9 = "under 9"
if < 11 = "under 11"
if < 13 = "under 13"
if < 15 = "under 15"
if < 17 = "under 17"
if < 19 = "under 19"
if >= 19 = "over 19"

The view should then output their DOB (10/06/1990), AgeYearEnd (19), ageGroup, (over 19)

Can someone please help me put this togeather?

Much appreciated
Richard

Richard Law

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 13:11:15

[code]
SELECT DOB,
DATEDIFF(yy,DOB,GETDATE()) AS AgeYearEnd,
CASE
WHEN DATEDIFF(yy,DOB,GETDATE()) < 9 THEN 'under 9'
WHEN DATEDIFF(yy,DOB,GETDATE()) < 11 THEN 'under 11'
WHEN DATEDIFF(yy,DOB,GETDATE()) < 13 THEN 'under 13'
WHEN DATEDIFF(yy,DOB,GETDATE()) < 15 THEN 'under 15'
...
WHEN DATEDIFF(yy,DOB,GETDATE()) >=19 THEN 'over 19'
END
AS ageGroup
FROM Gymnasts
[/code]

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2009-01-02 : 15:03:26

Thank you so much! Works a peach!

Richard Law

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-03 : 07:12:17

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources