Question: What are good ways, in the last query, to include batch 2 in the result as "processing"? Each batch has several child tasks. The overall status of the batch is what I'm looking for.

Task status meanings:

* 1=pending
* 2=processing
* 3=retrying
* 4=complete
* 5=failed

Batch status:

* Batch 1 is processing
* Batch 2 is in the middle of processing, but a pending task has not yet been picked up. It will be seconds before this happens, so I'm not interested in a "sleeping" type state.
* Batch 3 is processing with a retrying task
* Batch 4 is not processing because nothing has been picked up.
* Batch 5 is not processing because it is complete
* Batch 6 is not processing because has a failed task.

Code:

```
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t (batchId INT, taskStatus INT)
INSERT INTO #t(batchId, taskStatus) VALUES
       (1, 1),
       (1, 2),
       (1, 4),
       (2, 1),
       (2, 1),
       (2, 4),
       (2, 4),
       (3, 3),
       (3, 4),
       (4, 1),
       (4, 1),
       (5, 4),
       (5, 4),
       (6, 5),
       (6, 4)

SELECT 
    batchId, 
    1 processing
FROM #t
WHERE taskStatus IN (2,3)
GROUP BY batchId

```

Answer: 
I prefer the select subquery as the most straightforward to anything I've seen so far.

```
SELECT 
    t.batchId, 
    CASE 
        WHEN EXISTS(SELECT 1 FROM #t WHERE taskStatus IN (2,3) AND batchId = t.batchId) THEN 1 
        WHEN EXISTS(SELECT 1 FROM #t WHERE taskStatus=1 AND batchId = t.batchId) AND EXISTS(SELECT 1 FROM #t WHERE taskStatus=4 AND batchId = t.batchId) THEN 1
        ELSE 0 
    END processing  
FROM #t t
GROUP BY batchId

```
I interpret what you're saying to mean that you essentially have 3 different "batch statuses" that roughly equate to your task statuses of Pending, Processing, and Complete.

Therefore, for any given task status you need to know 2 things:

1. Does this task status indicate that the task has been started?
2. Does this task status indicate that the task has been completed?

If you have a TaskStatus table to define those statuses, then you could add some flag columns to that table, as I've done below:

```
CREATE TABLE TaskStatus (
    taskStatusId INT,
    taskStatusName VARCHAR(50),
    taskStarted TINYINT,
    taskCompleted TINYINT
);
INSERT INTO TaskStatus values (1, 'Pending', 0, 0);
INSERT INTO TaskStatus values (2, 'Processing', 1, 0);
INSERT INTO TaskStatus values (3, 'Retrying', 1, 0);
INSERT INTO TaskStatus values (4, 'Complete', 1, 1);

```

Now you can derive the "batch status" using logic as defined in the CASE statement of the query below:

```
select
  Task.batchId,
  CASE
    WHEN max(TaskStatus.taskStarted) = 0 AND min(TaskStatus.taskCompleted) = 0 THEN 1
    WHEN max(TaskStatus.taskStarted) > 0 AND min(TaskStatus.taskCompleted) = 0 THEN 2
    WHEN max(TaskStatus.taskStarted) > 0 AND min(TaskStatus.taskCompleted) > 0 THEN 4
  END as batchStatusId
FROM Task
INNER JOIN TaskStatus on
  Task.taskStatus = TaskStatus.taskStatusId
group by
  Task.batchId

```

[sqlfiddle sample](http://sqlfiddle.com/#!2/a7006/2/0)

In the query above, I'm using the value 1 to indicate a "batch status" of Pending, 2 to indicate a "batch status" of Processing, and 4 to indicate a "batch status" of Complete. You could create a BatchStatus table to truly define these values, though it wouldn't strictly be necessary.

If you need to derive other "batch statuses" based on other combinations of task statuses, then you would need additional flag fields on the TaskStatus table and more complicated logic in the CASE statement.