<div>
Batches? &nbsp;We don't need no stinkin' batches!! 
 
&gt;In this case I would only want batch 1, because all the claims have status = success. 
There's probably a more graceful way to pull this off, but below should work 
 

<pre><code class="code-1 nolinks" id="code-20-41365730-1">SELECT BatchID
FROM YourTable yt
 LEFT JOIN (
 SELECT BatchID, COUNT(BatchID) as non_success_count
 FROM YourTable
 WHERE ClaimStatus &lt;&gt; 'Success' ) ytns ON yt.BatchID = ytns.BatchID 
WHERE ISNULL(ytns.non_success_count, 0) = 0
</code></pre>
</div>

<div>
<pre><code class="code-10 nolinks" id="code-20-41365753-1">WITH cteBatch AS
(
	SELECT BatchID,
		SUM(CASE WHEN ClaimStatus = 'Success' THEN 1 ELSE 0 END) AS SuccessCount,
		SUM(CASE WHEN ClaimStatus = 'Fail' THEN 1 ELSE 0 END) AS FailCount
	FROM myTable
	GROUP BY BatchID
)
SELECT BatchID
FROM cteBatch
WHERE FailCount = 0
</code></pre>
</div>

<div>
Another option: 
 

<pre><code class="code-10 nolinks" id="code-20-41365766-1">DECLARE @Batch TABLE
(
	BatchID		INT,
	ClaimID		VARCHAR(10),
	ClaimStatus	VARCHAR(10)
);

INSERT INTO @Batch (BatchID, ClaimID, ClaimStatus)
VALUES
	(1, '1A', 'Success'),
	(1, '1B', 'Success'),
	(2, '2A', 'Fail'),
	(2, '2B', 'Fail'),
	(3, '3A', 'Success'),
	(3, '3B', 'Fail');

SELECT BatchID
FROM @Batch
GROUP BY BatchID
HAVING SUM(CASE WHEN ClaimStatus = 'Fail' THEN 1 ELSE 0 END) = 0
</code></pre>
</div>

<div>
^^^ &nbsp;Good ones ... learning something new every day
</div>

<div>
All replies were great but this worked the best for me! 
 
Thanks guys!
</div>

<div>
<div class="content wysiwyg-content">
Hi, 
 
I have a table with the following columns and data: 
 
&nbsp;BatchID ClaimID &nbsp; ClaimStatus 
&nbsp; &nbsp;1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 1A &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp;Success 
&nbsp; &nbsp;1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 1B &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp;Success 
&nbsp; &nbsp;2 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 2A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Fail 
&nbsp; &nbsp;2 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 2B &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Fail 
&nbsp; &nbsp;3 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;3A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Success 
&nbsp; &nbsp;3 &nbsp; &nbsp; &nbsp;3B &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Fail 
 
I would like to select the batches that have no claims failed. 
 
In this case I would only want batch 1, because all the claims have status = success. I don't want batch 2 because both claims have status = fail and I don't want batch 3 because 1 of the claims has status = fail. 
 
There could be multiple claims in a batch. That means that even if I had a batch with 10 claims and 9 had status = success and 1 had status = fail, I would not want to select that batch. 
 
Help please :) 
 
Thanks
</div>
</div>

Hi,

I have a table with the following columns and data:

 BatchID ClaimID   ClaimStatus 
   1 	   1A  	     Success 
   1 	   1B  	     Success 
   2 	   2A              Fail 
   2 	   2B              Fail 
   3	   3A              Success 
   3      3B              Fail 

I would like to select the batches that have no claims failed.

In this case I would only want batch 1, because all the claims have status = success. I don't want batch 2 because both claims have status = fail and I don't want batch 3 because 1 of the claims has status = fail.

There could be multiple claims in a batch. That means that even if I had a batch with 10 claims and 9 had status = success and 1 had status = fail, I would not want to select that batch.

Help please :)

Thanks

SQL Question

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Microsoft SQL Server 2008

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.