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
 Transact-SQL (2000)
 CASE question

Author  Topic 

ricks1683
Starting Member

14 Posts

Posted - 2007-07-12 : 10:05:28
Hi again. Qucik question....

Is it possible to create a CASE statement with a list of conditions? I'm not sure if i worded that properly so i'll give an example of what i'm asking for....

(CASE FieldA WHEN IN(Cond1, Cond2, ... , CondN) THEN 'Result1' ELSE 'Result2' END)

Is something like that possible? I can get around this using a temporary table but i'd rather not go that route. Any help would be appreicated!

Thanks!

Rick

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-12 : 10:43:59
More concrete examples would help...read the hint link in my sg...BUT

CASE WHEN Condition THEN Value
WHEN nextcondition THE Value
ELSE Value END



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

ricks1683
Starting Member

14 Posts

Posted - 2007-07-12 : 11:00:08
Brett, Here's the example more specifically....

Select
(Case ReportingDays
WHEN IN (1, 8, 15, 22, 29) then 'Day1'
WHEN IN (2, 9, 16, 23, 30) then 'Day2'
WHEN IN (3, 10, 17, 24, 31) then 'Day3'
WHEN IN (4, 11, 18, 25) then 'Day4'
WHEN IN (5, 12, 19, 26) then 'Day5'
WHEN IN (6, 13, 20, 27) then 'Day6'
ELSE 'Day7' END) as Band
from Losses


Thanks!
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-12 : 11:03:37
If you use CASE expression WHEN, then you can only compare the exact value and you cant use <,>,etc
Always use CASE WHEN expression
Refer this for more info
http://sqlteam.com/forums/topic.asp?TOPIC_ID=86290

Your query should be

Select
Case WHEN ReportingDays IN (1, 8, 15, 22, 29) then 'Day1'
WHEN ReportingDays IN (2, 9, 16, 23, 30) then 'Day2'
.
.
ELSE 'Day7' END) as Band
from Losses


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ricks1683
Starting Member

14 Posts

Posted - 2007-07-12 : 11:19:29
Madhivanan,

Thanks for the help! It was the repitition of CASE WHEN ReportingDays on each line that i was missing. THanks!

Rick
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-12 : 11:41:09
[code]
USE Northwind
GO

CREATE TABLE myTable99(ReportingDays int)
GO

INSERT INTO myTable99(ReportingDays)
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
GO


SELECT ReportingDays
, CASE WHEN ReportingDays IN (1, 8, 15, 22, 29) THEN 'Day1'
WHEN ReportingDays IN (2, 9, 16, 23, 30) THEN 'Day2'
WHEN ReportingDays IN (3, 10, 17, 24, 31) THEN 'Day3'
WHEN ReportingDays IN (4, 11, 18, 25) THEN 'Day4'
WHEN ReportingDays IN (5, 12, 19, 26) THEN 'Day5'
WHEN ReportingDays IN (6, 13, 20, 27) THEN 'Day6'
ELSE 'Day7'
END AS Band
FROM myTable99
GO

DROP TABLE myTable99
GO

[/code]


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

ricks1683
Starting Member

14 Posts

Posted - 2007-07-12 : 12:06:01
Thanks all! I've got this up and running now. I appreciate the help!

Rick
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-12 : 15:24:49
SELECT 'Day' + CAST(CASE WHEN ReportingDays % 7 = 0 THEN 7 ELSE ReportingDays % 7 END AS CHAR(1))


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -