with tasksOutstanding as ( select distinct subjectId as claimID from tbl_WRK_Tasks with (nolock) where subjectType = 'TMC' and ownerId = and ownerType = and disposition not in ( 'Z', 'D' ) and subjectID in ( select claimId from tbl_TPM_Claims with (nolock) where ownerId = and ownerType = and partnerId = and partnerType = ) ), taskTemplates as ( select taskTemplateId from tbl_WRK_TaskTemplates with (nolock) where subjectType = 'TMC' and ownerId = 0 and ownerType = '*' and taskType = 'WRK' ), tasks as ( select t.taskId, floor( 10191817 * sqrt( t.taskId * log10( t.taskId ) ) ) as taskTk, t.taskType, t.taskTypeId, t.ownerId, t.ownerType, t.partnerId, t.partnerType, t.partnerContactId, t.subjectId, t.subjectType, t.sourceId, t.sourceType, t.priority, t.taskDescription, t.createDate, t.createdBy, t.updateDate, t.updatedBy, t.disposition, t.dispositionUser, dispositionDate, case when t.disposition = '?' then 'To Do' when t.disposition = 'A' then 'In Progress' when t.disposition = 'X' then 'Complete' else t.disposition end as status, t.reminderDays, t.requestedBy, t.assignedTo, t.dueDate, c.firstname createdByFirstName, c.lastName createdByLastName, c.email createdByEmail, c.firstName + ' ' + c.lastName sendFrom, u.firstname updatedByFirstName, u.lastName updatedByLastName, u.email updatedByEmail, r.firstname requestedByFirstName, r.lastName requestedByLastName, r.email requestedByEmail, a.firstname assignedToFirstName, a.lastName assignedToLastName, a.email assignedToEmail, a.firstName + ' ' + a.lastName assignedToFullName, d.firstname dispositionUserFirstName, d.lastName dispositionUserLastName, d.email dispositionUserEmail, case when t.disposition = 'X' then 0 else datediff( d, getDate(), coalesce( t.dueDate, dateadd( yy, 5, t.createDate )) ) end daysRemaining, t.priority * 10000 + case when t.disposition = 'X' then 0 else datediff( d, getDate(), coalesce( t.dueDate, dateadd( yy, 5, t.createDate )) ) end sortRank, t.taskTemplateId, t.jsonData, t.acknowledged, ( select requestTime from tbl_WRK_UserRequests with (nolock) where ownerID = t.ownerId and ownerType = t.ownerType and toUserId = t.assignedTo and fromUserId = t.createdBy and config like concat( '%TASKID=', t.taskId, '%' ) ) requestTime, ( select requestSent from tbl_WRK_UserRequests with (nolock) where ownerID = t.ownerId and ownerType = t.ownerType and toUserId = t.assignedTo and fromUserId = t.createdBy and config like concat( '%TASKID=', t.taskId, '%' ) ) requestSent from tbl_WRK_Tasks t with (nolock) left outer join tbl_FSPro_members c with (nolock) on c.fspro_userid = t.createdBy left outer join tbl_FSPro_members u with (nolock) on u.fspro_userid = t.updatedBy left outer join tbl_FSPro_members r with (nolock) on r.fspro_userid = t.requestedBy left outer join tbl_FSPro_members a with (nolock) on a.fspro_userid = t.assignedTo left outer join tbl_FSPro_members d with (nolock) on d.fspro_userid = t.dispositionUser where t.ownerId = and t.ownerType = and t.disposition in ( ) and t.subjectType = 'TMC' and t.subjectId in ( select claimId from tasksOutstanding ) ) #forJSON( 'tasks', attributes.datastore, 'sortRank' )#