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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply