January 13, 2008 at 1:30 pm
I havea table containing 5 columns.
ColumnCount 1ML 2ML 3ML 4ML
0 1 3 6 2
I need to evaluate the four ML columns and place the result in the ColumnCount column. The following code indicates how I need to evaluate each row in the table. What I don’t know is how to have this code applied to each row in the table? How do I embed this in a SELECT?
DECLARE @MatchLevel int, @MatchCount int
@MatchLevel = 3
IF 1ML <= @MatchLevel
BEGIN
@MatchCount = @MatchCount + 1
END
IF 2ML <= @MatchLevel
BEGIN
@MatchCount = @MatchCount + 1
END
IF 3ML <= @MatchLevel
BEGIN
@MatchCount = @MatchCount + 1
END
IF 4ML <= @MatchLevel
BEGIN
@MatchCount = @MatchCount + 1
END
ColumnCount = @MatchCount
@MatchCount = 0
The desired result for the example row would be:
ColumnCount 1ML 2ML 3ML 4ML
3 1 3 6 2
January 13, 2008 at 6:20 pm
Maybe Something like...
Declare @MatchLevel int
Set @MatchLevel = 1
Select ColumnCount =
Case when 1ML <= @MatchLevel Then 1 Else 0 End
+ Case when 2ML <= @MatchLevel Then 1 Else 0 End
+ Case when 3ML <= @MatchLevel Then 1 Else 0 End
+ Case when 4ML <= @MatchLevel Then 1 Else 0 End
,1ML
,2ML
,3ML
,4ML
From MyTable
January 13, 2008 at 7:15 pm
Great solution. Much simpler. Thank you
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy