DECLARE @PieceCount TABLE 
(
	specie INT, 
	thick  INT,
	grade  INT,
	length  INT,
	width  INT,
	moisture  INT,
	surface  INT,
	pack  INT,
	ply  INT,
	piececount INT
)

INSERT @Piececount
SELECT 1, 1, 0, 1, 1, 1, 0, 0, 0, 10
UNION ALL SELECT 1, 1, 1, 1, 1, 1, 7, 0, 0, 20

DECLARE @Product TABLE
(
	specie INT, 
	thick  INT,
	grade  INT,
	length  INT,
	width  INT,
	moisture  INT,
	surface  INT,
	pack  INT,
	ply  INT,
	description VARCHAR(50)
)

INSERT @Product
SELECT 1, 1, 1, 1, 1, 1, 1, 1, 1, 'xxxxx'
UNION ALL SELECT 1, 1, 1, 1, 1, 1, 7, 1, 1, 'sssss'
UNION ALL SELECT 1, 1, 1, 1, 1, 1, 2, 1, 1, 'ttttt'

SELECT 
	Description,
	PieceCount
FROM
	(
		SELECT
			P.Description,
			C.PieceCount,
			--) AS Confidence,
			ROW_NUMBER() OVER
			( 
				PARTITION BY 
					P.Description 
				ORDER BY 
					-- Used to weight the match
					MAX(CASE WHEN P.specie = C.specie THEN 1 ELSE 0 END 
					+ CASE WHEN P.thick = C.thick THEN 1 ELSE 0 END
					+ CASE WHEN P.grade = C.grade THEN 1 ELSE 0 END
					+ CASE WHEN P.length = C.length THEN 1 ELSE 0 END
					+ CASE WHEN P.width = C.width THEN 1 ELSE 0 END
					+ CASE WHEN P.moisture = C.moisture THEN 1 ELSE 0 END
					+ CASE WHEN P.surface = C.surface THEN 1 ELSE 0 END
					+ CASE WHEN P.pack = C.pack THEN 1 ELSE 0 END
					+ CASE WHEN P.ply = C.ply THEN 1 ELSE 0 END) DESC
			) AS RowNum
		FROM
			@Product AS P
		INNER JOIN
			@PieceCount AS C
			ON (P.specie = C.specie OR C.specie = 0)
			AND (P.thick = C.thick OR C.thick = 0)
			AND (P.grade = C.grade OR C.grade = 0)
			AND (P.length = C.length OR C.length = 0)
			AND (P.width = C.width OR C.width = 0)
			AND (P.moisture = C.moisture OR C.moisture = 0)
			AND (P.surface = C.surface OR C.surface = 0)
			AND (P.pack = C.pack OR C.pack = 0)
			AND (P.ply = C.ply OR C.ply = 0)
		GROUP BY
			P.Description,
			C.PieceCount
	) AS Temp
WHERE
	RowNum = 1