SELECT
CASE WHEN field1 IS NOT NULL THEN '1' ELSE '0' END AS field1,
CASE WHEN field2 IS NOT NULL THEN '1' ELSE '0' END AS field2,
CASE WHEN field3 IS NOT NULL THEN '1' ELSE '0' END AS field3,
CASE WHEN field4 IS NOT NULL THEN '1' ELSE '0' END AS field4,
CASE WHEN field5 IS NOT NULL THEN '1' ELSE '0' END AS field5,
sum( field1 + field2 + field3 + field4 + field5 ) as Score
FROM myTable

DECLARE @myTable TABLE (field1 int, field2 int, field3 int, field4 int, field5 int)
INSERT @myTable (field1, field2, field3, field4, field5)
SELECT 1, 1, 1, 1, 1 UNION
SELECT NULL, NULL, NULL, NULL, NULL UNION
SELECT 2, NULL, 2, 2, 2 UNION
SELECT 3, NULL, NULL, 3, 3 UNION
SELECT 4, 4, 4, NULL, 4 UNION
SELECT 0, NULL, NULL, 0, NULL UNION
SELECT 3, 3, 3, 3, NULL

SELECT field1+field2+field3+field4+field5 as Score
FROM (SELECT
CASE WHEN field1 IS NOT NULL THEN 1 ELSE 0 END AS field1,
CASE WHEN field2 IS NOT NULL THEN 1 ELSE 0 END AS field2,
CASE WHEN field3 IS NOT NULL THEN 1 ELSE 0 END AS field3,
CASE WHEN field4 IS NOT NULL THEN 1 ELSE 0 END AS field4,
CASE WHEN field5 IS NOT NULL THEN 1 ELSE 0 END AS field5
FROM @myTable) x

SELECT *
FROM myTable
where CASE WHEN field1 IS NOT NULL THEN 1 ELSE 0 END +
	CASE WHEN field2 IS NOT NULL THEN 1 ELSE 0 END +
	CASE WHEN field3 IS NOT NULL THEN 1 ELSE 0 END +
	CASE WHEN field4 IS NOT NULL THEN 1 ELSE 0 END +
	CASE WHEN field5 IS NOT NULL THEN 1 ELSE 0 END >= 4

quote:

---

Originally posted by Bustaz Kool

SELECT *
FROM myTable
where CASE WHEN field1 IS NOT NULL THEN 1 ELSE 0 END +
	CASE WHEN field2 IS NOT NULL THEN 1 ELSE 0 END +
	CASE WHEN field3 IS NOT NULL THEN 1 ELSE 0 END +
	CASE WHEN field4 IS NOT NULL THEN 1 ELSE 0 END +
	CASE WHEN field5 IS NOT NULL THEN 1 ELSE 0 END >= 4

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno

---