trouble with case statement - 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)

trouble with case statement

Author

Topic

jaskalirai
Starting Member

31 Posts

Posted - 2007-10-15 : 09:57:30

what is wrong with the following case statement

select sum(case sum (kornerwait_weeks_endofmonth) when >= o then o end [0 weeks & over])
when >4 then 4 end [4 weeks and over]
when >6 then 6 end [6 weeks and over]
when >8 then 8 end [8 weeks and over]
when >11 then 11 end [11 weeks and over]
when >13 then 14 end [13 weeks and over]
end
from dbo.table 1

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-15 : 10:00:56

Syntax entirely seems to be wrong. What you want to do?

Probably you meant something like this:

select 
case When sum (kornerwait_weeks_endofmonth) >= 0 then 0 end [0 weeks & over],
case When sum (kornerwait_weeks_endofmonth) >4 then 4 end [4 weeks and over],
case When sum (kornerwait_weeks_endofmonth) >6 then 6 end [6 weeks and over],
...
from dbo.table 1

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-15 : 10:18:27

You could also try to drop the table alias "1".
Or at least call it "[1]".

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-15 : 10:19:52

And reverse the order of checking.
As of now, ALL checks are validated by first CASE, because ALL statments are >= 0...

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-15 : 10:38:15

The big issue is that CASE is an *expression*, not a statement -- you are confusing how it works. It cannot contain or return blocks of code, and it does not dynamically alter your SELECT in any way. It just evaluates and returns expressions, like a function.

read this carefully: http://weblogs.sqlteam.com/jeffs/archive/2007/05/03/60195.aspx

Also, read about it in Books On-Line or other SQL books of your choice, and be sure to refer to it as a CASE *expression* in the future to help avoid confusion.

- Jeff
http://weblogs.sqlteam.com/JeffS

jaskalirai
Starting Member

31 Posts

Posted - 2007-10-15 : 11:55:47

SELECT KORNERWAIT_WEEKS_ENDOFMONTH, COUNT(*)AS NUMBER_OF_PATIENTS
FROM dbo.TABLE1
WHERE ACTIVEWAITINGLIST = 1
GROUP BY KORNERWAIT_WEEKS_ENDOFMONTH

above is my statment below is my case

CASE WHEN SUM (KORNERWAIT_WEEKS_ENDOFMONTH) >= 0 THEN sum(Number_Of_Patients) END [O WEEKS & OVER],
CASE WHEN SUM (KORNERWAIT_WEEKS_ENDOFMONTH) >= 4 THEN sum(Number_Of_Patients) END [4 WEEKS & OVER],
CASE WHEN SUM (KORNERWAIT_WEEKS_ENDOFMONTH) >= 6 THEN sum(Number_Of_Patients) END [6 WEEKS & OVER],
CASE WHEN SUM (KORNERWAIT_WEEKS_ENDOFMONTH) >= 8 THEN sum(Number_Of_Patients) END [8 WEEKS & OVER],
CASE WHEN SUM (KORNERWAIT_WEEKS_ENDOFMONTH) >= 11 THEN sum(Number_Of_Patients) END [11 WEEKS & OVER],
CASE WHEN SUM (KORNERWAIT_WEEKS_ENDOFMONTH) >= 13 THEN sum(Number_Of_Patients) END [13 WEEKS & OVER]
FROM

table1

i want to display the number of patients with in the case expressions if that makes sense. i am new to sql as you have gathered. so for example >=4 will have x many patients etc

Kristen
Test

22859 Posts

Posted - 2007-10-15 : 13:21:49

Something like this perhaps?


SUM(CASE WHEN KORNERWAIT_WEEKS_ENDOFMONTH >= 0 THEN 1 ELSE 0 END) [O WEEKS & OVER],
SUM(CASE WHEN KORNERWAIT_WEEKS_ENDOFMONTH >= 4 THEN 1 ELSE 0 END) [4 WEEKS & OVER],
...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources