select bc.batchClass as batchClass, bc.description as description, case when bc.enabled = 'Y' then 1 else 0 end as [enabled], bc.batchPriority as batchPriority, -- ( select count(*) from tbl_EAI_inboundBatches b with (nolock) where b.ownerId > 0 and b.status = 'PENDING' and b.batchClass = bc.batchClass ) as pendingBatchCountOld, ( select count(*) from tbl_EAI_InboundBatches b with (nolock) left outer join tbl_EAI_InboundBatches db with (nolock) on db.batchId = b.dependentBatchId where b.batchClass = bc.batchClass and b.queueStatus = 0 and b.batchTime <= { fn now() } and ( b.expireTime is null or b.expireTime >= { fn now() } or b.expireTime >= '09/08/2017' ) and ( db.batchId is null or db.queueStatus = 300 ) ) pendingBatchCount, ( select count(*) from tbl_EAI_InboundBatches b with (nolock) left outer join tbl_EAI_InboundBatches db with (nolock) on db.batchId = b.dependentBatchId where b.batchClass = bc.batchClass and b.queueStatus = 0 and b.batchTime > { fn now() } and ( b.expireTime is null or b.expireTime >= { fn now() } or b.expireTime >= '09/08/2017' ) and ( db.batchId is null or db.queueStatus = 300 ) ) futureBatchCount, ( select count(*) from tbl_EAI_inboundBatches b with (nolock) where b.ownerId > 0 and b.status = 'RUNNING' and b.batchClass = bc.batchClass ) as runningBatchCount from tbl_EAI_inboundBatchClasses bc with (nolock) where 1 = 2 order by batchPriority desc, description for json path, include_null_values