declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @userId int = ; declare @today datetime = ; with tasks as ( select t.taskId, case when t.disposition = 'X' then 'Completed' when t.disposition = 'Z' then 'Cancelled' when t.disposition = 'H' then 'On Hold' when t.disposition = '?' then case when t.dueDate is null then 'Open' when t.dueDate < @today then 'Past Due' else 'Open' end else t.disposition end as statusDisplay /* , t.taskType, cast( t.priority as int ) priority, t.ownerId, t.ownerType, t.subjectId, t.subjectType, t.sourceId, t.sourceType, t.taskDescription, t.createDate, t.createdBy, t.updateDate, t.updatedBy, t.disposition, t.dispositionUser, t.dispositionDate, t.reminderDays, t.requestedBy, r.firstName + ' ' + r.lastName as requestedByFullName, t.assignedTo, a.firstName + ' ' + a.lastName as assignedToFullName, t.dueDate, t.dueDateNotification, t.partnerId, t.partnerType, t.partnerContactId, t.reminderSent, t.taskComment, t.taskTemplateId, t.workflowRequestId, t.acknowledged, t.taskTypeId, t.repeating, t.frequency, t.repeatUntil, t.sourceTaskId */ from tbl_WRK_Tasks t with (nolock) /* inner join tbl_FSPro_Members a with (nolock) on a.fspro_userId = t.assignedTo and a.ownerId > 0 inner join tbl_FSPro_Members r with (nolock) on r.fspro_userId = t.requestedBy and r.ownerId > 0 */ where t.ownerId = @ownerId and t.ownerType = @ownerType and ( t.assignedTo = @userId or t.requestedBy = @userId ) and t.assignedTo = and t.createdBy = and t.dueDate >= and t.dueDate <= ), dispositions as ( select b.* from (VALUES ( 'Past Due' ), ( 'Open'), ( 'On Hold'), ( 'Complete' ), ( 'Cancelled' ) ) AS b( display) ), tasksStatus as ( select d.display , coalesce( count( taskId), 0 ) as taskNumbers from dispositions d left outer join tasks t on t.statusDisplay = d.display group by d.display ) #forJSON( 'tasksStatus', attributes.datastore, 'display' )#