| Author |
 Topic  |
|
|
ricks1683
Starting Member
USA
14 Posts |
 Posted - 07/12/2007 : 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 |
|
|
ricks1683
Starting Member
USA
14 Posts |
Posted - 07/12/2007 : 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! |
 |
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 07/12/2007 : 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 |
Edited by - madhivanan on 07/12/2007 11:04:05 |
|
|
|
ricks1683
Starting Member
USA
14 Posts |
Posted - 07/12/2007 : 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 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 07/12/2007 : 11:41:09
|
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
|
|
|
|
ricks1683
Starting Member
USA
14 Posts |
Posted - 07/12/2007 : 12:06:01
|
Thanks all! I've got this up and running now. I appreciate the help!
Rick |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 07/12/2007 : 15:24:49
|
SELECT 'Day' + CAST(CASE WHEN ReportingDays % 7 = 0 THEN 7 ELSE ReportingDays % 7 END AS CHAR(1))
Peter Larsson
Helsingborg, Sweden |
|
|
| |
Topic |
|
|
|
CASE question
