sql server - Derive grouped meaning from multiple rows -

what ways, in last query, include batch 2 in result "processing"? each batch has several child tasks. overall status of batch i'm looking for.

task status meanings:

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

batch status:

• batch 1 processing
• batch 2 in middle of processing, pending task has not yet been picked up. seconds before happens, i'm not interested in "sleeping" type state.
• batch 3 processing retrying task
• batch 4 not processing because nothing has been picked up.
• batch 5 not processing because complete
• batch 6 not processing because has failed task.

code:

if object_id('tempdb..#t') not null drop table #t create table #t (batchid int, taskstatus int) insert #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 #t taskstatus in (2,3) group batchid 

update

select batchid, 1 processing   table1  group batchid having sum(case when taskstatus in(2,3) 1 else 0 end) > 0      or (sum(case when taskstatus in(1,4) 1 else 0 end) = count(*)     , count(distinct taskstatus) = 2) 

here sqlfiddle demo.

original answer original version of question:

select batchid, 1 processing   table1  group batchid having count(*) * 4 > sum(taskstatus) 

this query returns batches incomplete tasks.

here sqlfiddle demo.