case question - 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

General SQL Server Forums

New to SQL Server Programming

case question

Author

Topic

rob41
Yak Posting Veteran

67 Posts

Posted - 2010-09-13 : 13:33:24

If I wanted to have a column such as [Time] take a variable from multiple columns that could only be 1, 0, or null.
If the variable is 1 then [Time] column should = 'Late' if variable in multiple columns is anything other than 1 on [Time] should = 'On Time' any suggestions of how to do this with a case statement or am I barking up the wrong tree. Here's a sample of the code i'm working with below

select [ship].[load #],
[ship].[ship id],
[ship].[ccc], --******* this column = 1, 0, or null ****
[ship].[ddd], --******* this column = 1, 0, or null ****
[ship].[eee], --******* this column = 1, 0, or null ****

is it possible to do it like this

select [ship].[load #],
[ship].[ship id],
[ship].[ccc],
[ship].[ddd],
[ship].[eee],
case when [ccc] = 1, when [ddd] = 1, when [eee] = 1
then [Time] = 'Late'
end
case when [ccc] <> 1, when [ddd] <> 1, when [eee] <> 1
then [Time] = 'On Time'
end
from [ship]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-13 : 13:38:33

select
load,
id,
ccc,
ddd,
eee,
case
when ccc=1 and ddd=1 and eee=1 then 'Late'
else 'On Time'
end as [Time]
from ship

No, you're never too old to Yak'n'Roll if you're too young to die.