Thank you for the advice. I'm still very new at SQL but hopefully this will help clarify what I am looking to do:

Create Table #ClaimSummary

(ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

ClaimNumber varchar,)

TotalPaidAmount money)

This would be the first 9 records for table #ClaimSummary

IdClaimNumberTotalPaidAmount

112300 10.00

212301 0.00

312302 15.00

445600 10.00

545601 15.00

645602 0.00

799900 0.00

899901 15.00

999902 20.00

Here is the code I am currently using. This gives me part of what I want, but unfortunately if ANY claim beginning with the same 3 numbers has a $0 totalpaidamount then it will show in the results. And in the case of claim beginning 999, I would want to exclude that from the result set because the last 2 consecutive claims both had a > 0 totalpaidamount.

SELECT DISTINCT SUBSTRING(CS.ClaimNumber,1,3) AS PartialClaimNumber

FROM #ClaimSummary AS CS

WHERE CS.TotalPaidAmount = 0

GROUP BY SUBSTRING(CS.ClaimNumber,1,3)

How can I write the code to prevent SUBSTRING(CS.ClaimNumber,1,3) = 999 from showing in my result set?

Thank you!