Search code examples
sql-server-2000

Select rows where x of y columns meet criteria

Working with a table that is perhaps not terribly normalized1, like so:

ID   S1 S2 S3 S4 S5 S6 S7 S8 S9
101  3  2  1  3  4  3  5  3  3
102  4  3  4  3  2  3  2  3  2
103  3  2  3  4  3  2  3  3  4

(The S# columns are scores, basically.) I need to return rows where one of two conditions are met:

  1. Any score is 1. (This part's easy, albeit a bit tedious.)
  2. Any three or more scores are 2. (This part's not.)

(So in the sample data, row 1 meets the first criterion, and row 2 meets the second criterion. Row 3 doesn't meet either.)

The problem is that I need to shoehorn this into an existing mechanism, which means no SUM(CASE WHEN S1 = '2' OR S2 = '2'... THEN 1 ELSE 0 END), because there's no aggregation/grouping happening: this needs to be a straight condition that I can add to an existing WHERE clause.

The other problem is that this is on SQL Server 2000, so most of the fancy-shmancy additions that are available in 2008+ are not an option.

Is there any way to do this?

1 That's called an understatement, folks. :)

Solution

  • One way would be 

    WHERE 3 <=
          CASE WHEN S1 = 2 THEN 1 ELSE 0 END +
          CASE WHEN S2 = 2 THEN 1 ELSE 0 END +
          CASE WHEN S3 = 2 THEN 1 ELSE 0 END +
          CASE WHEN S4 = 2 THEN 1 ELSE 0 END +
          CASE WHEN S5 = 2 THEN 1 ELSE 0 END +
          CASE WHEN S6 = 2 THEN 1 ELSE 0 END +
          CASE WHEN S7 = 2 THEN 1 ELSE 0 END +
          CASE WHEN S8 = 2 THEN 1 ELSE 0 END +
          CASE WHEN S9 = 2 THEN 1 ELSE 0 END

    Or (untested on SQL Server 2000)

    WHERE  3 <= (SELECT COUNT(*)
             FROM   (SELECT S1
                     UNION ALL
                     SELECT S2
                     UNION ALL
                     SELECT S3
                     UNION ALL
                     SELECT S4
                     UNION ALL
                     SELECT S5
                     UNION ALL
                     SELECT S6
                     UNION ALL
                     SELECT S7
                     UNION ALL
                     SELECT S8
                     UNION ALL
                     SELECT S9)D(S)
             WHERE  S = 2)