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.
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 |
|
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 Bandfrom LossesThanks! |
|
|
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 <,>,etcAlways use CASE WHEN expressionRefer this for more infohttp://sqlteam.com/forums/topic.asp?TOPIC_ID=86290Your query should beSelectCase WHEN ReportingDays IN (1, 8, 15, 22, 29) then 'Day1' WHEN ReportingDays IN (2, 9, 16, 23, 30) then 'Day2' ..ELSE 'Day7' END) as Bandfrom LossesMadhivananFailing to plan is Planning to fail |
|
|
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 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-07-12 : 11:41:09
|
[code]USE NorthwindGOCREATE TABLE myTable99(ReportingDays int)GOINSERT INTO myTable99(ReportingDays)SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALLSELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALLSELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9GOSELECT 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 myTable99GODROP TABLE myTable99GO[/code]Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
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 |
|
|
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 LarssonHelsingborg, Sweden |
|
|
|
|
|
|
|