title: How to count if all documents in a group have a value set to 1?
body: <p>In a document review tool, one can create "batches" of documents. A batch is a group of related documents, identified by GroupID.</p>
  
  <p>These groups of documents are presented to reviewers, who update a field called Testcompleted. This field has 3 possible states: 1, 0 or null. The number of documents in a group varies.</p>
  
  <p>In the example below I have 3 groups ("batches") of documents. The first batch (batch_0001), for instance, has 2 documents (58 and 59).</p>
  
  <pre><code>#Document
  
  ArtifactID    Testcompleted    GroupID
  --------------------------------------
  58            1                4
  59            1                4
  60            null             6
  61            1                6
  62            null             7
  63            null             7
  64            null             7
  
  #DocumentBatch
  
  BatchArtifactID    DocumentArtifactID
  -------------------------------------
  66                 58
  66                 59
  67                 60
  67                 61
  68                 62
  68                 63
  68                 64
  
  
  #Batch
  
  ArtifactID    Name
  ------------------------
  66            batch_0001
  67            batch_0002
  68            batch_0003
  </code></pre>
  
  <p>I need to know when a batch is completed -- that is: when all documents in that batch have the Testcompleted field set to 1. In the example, this is the case for batch_0001.</p>
  
  <p>The output I am looking for is:</p>
  
  <pre><code>batch           documents       reviewed     completed
  ------------------------------------------------------
  batch_0001      2               2            yes
  batch_0002      2               1            no
  batch_0003      3               0            no
  </code></pre>
  
  <p>I started by joining the tables:</p>
  
  <pre><code>select
      *
  from
      #Document d
  
      left join #DocumentBatch db
      on db.DocumentArtifactID = b.ArtifactID
  
      left join #Batch b
      on db.BatchArtifactID = b.ArtifactID
  where
      d.Testcompleted = 1
  ;
  </code></pre>
  
  <p>This obviously does not return the result I need, but I am stuck. Some help on how to solve this would be greatly appreciated.</p>