CASE problem - 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 2000 Forums

SQL Server Development (2000)

CASE problem

Author

Topic

db_sysadmin
Starting Member

27 Posts

Posted - 2006-10-12 : 09:20:52

Hi there,

I run into a problem with the CASE sentence:
when I define the "WHEN" Clause like this...

'field' = CASE
WHEN (datediff(year, BirthDate, getdate())BETWEEN 1 AND 15) THEN 'Children Between 1 a 15'

WHEN (datediff(year, FechNac_Fam, getdate()) BETWEEN 15 AND 19) AND PersonSex = 'F' THEN 'Female children between(1 a 15)'

WHEN ...
END

when the row matches with the first "WHEN clause" it won't evaluate the following ones...
so what can I do for it to evaluate them all?

Thanks in advance!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-12 : 09:24:13

Why do you need to match with other case?

You will have to write separate case statement for each condition then !

'field' = CASE
WHEN (datediff(year, BirthDate, getdate())BETWEEN 1 AND 15) THEN 'Children Between 1 a 15' end + CASE WHEN (datediff(year, FechNac_Fam, getdate()) BETWEEN 15 AND 19) AND PersonSex = 'F' THEN 'Female children between(1 a 15)' + CASE ...

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-13 : 10:18:30

Read about Cross-tab reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-13 : 10:21:50

quote:
Originally posted by db_sysadmin

when the row matches with the first "WHEN clause" it won't evaluate the following ones...
so what can I do for it to evaluate them all?

You start with the most detailed and write the less detailed last.

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-13 : 12:30:19

Your logic for calculating age is flawed. In the example below, you would call someone only a day old one year old.

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. This function returns age in format YYYY MM DD.
Age Function F_AGE_YYYY_MM_DD:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729


select
	BirthDate,
	Today,
	Age = datediff(year, BirthDate, Today)
from
	( select
		BirthDate = convert(datetime,'20051231'),
		Today = convert(datetime,'20060101')
	) a

Results:


BirthDate                Today                    Age
------------------------ ------------------------ ----------- 
2005-12-31 00:00:00.000  2006-01-01 00:00:00.000  1

(1 row(s) affected)

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-13 : 12:33:37

quote:
Originally posted by harsh_athalye

Why do you need to match with other case?

You will have to write separate case statement for each condition then !

'field' = CASE
WHEN (datediff(year, BirthDate, getdate())BETWEEN 1 AND 15) THEN 'Children Between 1 a 15' end + CASE WHEN (datediff(year, FechNac_Fam, getdate()) BETWEEN 15 AND 19) AND PersonSex = 'F' THEN 'Female children between(1 a 15)' + CASE ...

Harsh Athalye
India.
"Nothing is Impossible"

Does your computer not have a carriage return key?

Makes the code a little easier to read when it isn't a mile wide.

CODO ERGO SUM

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-13 : 14:29:42

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by harsh_athalye

Why do you need to match with other case?

You will have to write separate case statement for each condition then !

'field' = CASE
WHEN (datediff(year, BirthDate, getdate())BETWEEN 1 AND 15) THEN 'Children Between 1 a 15' end + CASE WHEN (datediff(year, FechNac_Fam, getdate()) BETWEEN 15 AND 19) AND PersonSex = 'F' THEN 'Female children between(1 a 15)' + CASE ...

Harsh Athalye
India.
"Nothing is Impossible"

Does your computer not have a carriage return key?

Makes the code a little easier to read when it isn't a mile wide.

CODO ERGO SUM

Sorry MVJ !!

Usually I format the code, but can't help...
just wanted to beat Peter, as regards to time

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources