SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 CASE question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ricks1683
Starting Member

USA
14 Posts

Posted - 07/12/2007 :  10:05:28  Show Profile  Reply with Quote
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 - 07/12/2007 :  10:43:59  Show Profile  Reply with Quote
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

USA
14 Posts

Posted - 07/12/2007 :  11:00:08  Show Profile  Reply with Quote
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

India
22761 Posts

Posted - 07/12/2007 :  11:03:37  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Edited by - madhivanan on 07/12/2007 11:04:05
Go to Top of Page

ricks1683
Starting Member

USA
14 Posts

Posted - 07/12/2007 :  11:19:29  Show Profile  Reply with Quote
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 - 07/12/2007 :  11:41:09  Show Profile  Reply with Quote

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




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

USA
14 Posts

Posted - 07/12/2007 :  12:06:01  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 07/12/2007 :  15:24:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000