tasks log
{ts '2025-05-28 14:33:27'} SQL
{ts '2025-05-28 14:33:27'} SQL
declare @ownerId int = 1859 ;
    declare @ownerType varchar(3) = 'BRO' ;
    declare @userId int = 0 ;
    declare @today datetime = '2025-05-28' ;
    declare @currentUserId int = 5266075 ;


    with usersWatchList as (
        select distinct wl.ownerId, wl.ownerType, wl.subjectId as taskId
          from tbl_CRM_WatchList wl with (nolock)
         where ownerId = @ownerId
           and ownerType = @ownerType
           and subjectType = 'WRT'
           and active = 'Y'
           and fspro_userId = @currentUserId
    ),

    tasks as (

        select t.taskId,
            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,

            case when t.disposition = 'X' then 'Complete'
                 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.dispositionUser,
            d.firstName + ' ' + d.lastName as dispositionUserFullName,
            t.dispositionDate,
            t.reminderDays,
            t.requestedBy,
            r.firstName as requestedByFirstName,
            r.lastName as requestedByLastName,
            r.firstName + ' ' + r.lastName as requestedByFullName,
            t.assignedTo,
            a.firstName as assignedToFirstName,
            a.lastName as assignedToLastName,
            a.firstName + ' ' + a.lastName as assignedToFullName,
            t.dueDate,
            t.dueDateNotification,
            cast( case when t.disposition = '?' and t.dueDate < @today then 1 else 0 end as bit ) as pastDue,
            t.partnerId,
            t.partnerType,
            t.partnerContactId,
            case when t.partnerId is null then null
                else json_query( '{ "partnerType" : "' + coalesce( t.partnerType, '') + '", "partnerId":' + cast( t.partnerId as varchar ) + ', "contactId" : ' + case when nullif( t.partnerContactId, 0 ) is null then 'null' else cast( t.partnerContactId as varchar ) end + ' }' )
            end as connectedTo,
            t.reminderSent,
            t.taskComment,
            t.taskTemplateId,
            t.workflowRequestId,
            json_query( coalesce( t.jsonData, '{}' )) as jsonData,
            t.acknowledged,
            t.taskTypeId,
            p.projectId,
            p.projectName,
            t.repeating,
            t.frequency,
            coalesce( t.repeatFrom, t.dueDate ) as repeatFrom,
            t.repeatUntil,
            t.sourceTaskId,
            cast( case when wl.taskId is not null then 1 else 0 end as bit ) watching

            

        from tbl_WRK_Tasks t with (nolock)

        left outer join tbl_WRK_Projects p with (nolock)
          on p.ownerId = t.ownerId
          and p.ownerType = t.ownerType
          and p.projectId = t.projectId

        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

        left outer join tbl_FSPro_Members d with (nolock)
           on d.fspro_userId = t.dispositionUser
           and d.ownerId > 0

        left outer join usersWatchList wl with (nolock)
          on wl.ownerId = t.ownerId
          and wl.ownerType = t.ownerType
          and wl.taskId = t.taskId

        where t.ownerId = @ownerId
        and t.ownerType = @ownerType
        

    )

    


, tasks_mappedData as (

	select *
		
	  from tasks

)

, tasks_preData as (

	
	select *
	  from tasks_mappedData with (nolock)

	
	
	where 

	(
	

	(
	

	(
	

	(
	[requestedBy]
							= '5266075' 	
	)
or 

	(
	[assignedTo]
							= '5266075' 	
	)
	
	)
	
	)
and 

	(
	[statusDisplay]
							= 'Complete' 	
	)
	
	)



	)

	, tasks_data as (

		select * 
		        
		  from tasks_preData
		  

	)



	, tasks_return as (
		select * from tasks_data
		where 1 = 1


	order by dueDate  desc
		
		
		 offset 0 rows fetch next 50 rows only
	
	)

	
	, tasks_result as (
		select ( select count(*) from tasks_data ) as totalCount, 
			   
		       ( select * from tasks_return for json auto, include_null_values ) as data
		)


	select * from tasks_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
declare @ownerId int = 1859 ;
    declare @ownerType varchar(3) = 'BRO' ;
    declare @userId int = 0 ;
    declare @today datetime = '2025-05-28' ;
    declare @currentUserId int = 5266075 ;


    with usersWatchList as (
        select distinct wl.ownerId, wl.ownerType, wl.subjectId as taskId
          from tbl_CRM_WatchList wl with (nolock)
         where ownerId = @ownerId
           and ownerType = @ownerType
           and subjectType = 'WRT'
           and active = 'Y'
           and fspro_userId = @currentUserId
    ),

    tasks as (

        select t.taskId,
            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,

            case when t.disposition = 'X' then 'Complete'
                 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.dispositionUser,
            d.firstName + ' ' + d.lastName as dispositionUserFullName,
            t.dispositionDate,
            t.reminderDays,
            t.requestedBy,
            r.firstName as requestedByFirstName,
            r.lastName as requestedByLastName,
            r.firstName + ' ' + r.lastName as requestedByFullName,
            t.assignedTo,
            a.firstName as assignedToFirstName,
            a.lastName as assignedToLastName,
            a.firstName + ' ' + a.lastName as assignedToFullName,
            t.dueDate,
            t.dueDateNotification,
            cast( case when t.disposition = '?' and t.dueDate < @today then 1 else 0 end as bit ) as pastDue,
            t.partnerId,
            t.partnerType,
            t.partnerContactId,
            case when t.partnerId is null then null
                else json_query( '{ "partnerType" : "' + coalesce( t.partnerType, '') + '", "partnerId":' + cast( t.partnerId as varchar ) + ', "contactId" : ' + case when nullif( t.partnerContactId, 0 ) is null then 'null' else cast( t.partnerContactId as varchar ) end + ' }' )
            end as connectedTo,
            t.reminderSent,
            t.taskComment,
            t.taskTemplateId,
            t.workflowRequestId,
            json_query( coalesce( t.jsonData, '{}' )) as jsonData,
            t.acknowledged,
            t.taskTypeId,
            p.projectId,
            p.projectName,
            t.repeating,
            t.frequency,
            coalesce( t.repeatFrom, t.dueDate ) as repeatFrom,
            t.repeatUntil,
            t.sourceTaskId,
            cast( case when wl.taskId is not null then 1 else 0 end as bit ) watching

            

        from tbl_WRK_Tasks t with (nolock)

        left outer join tbl_WRK_Projects p with (nolock)
          on p.ownerId = t.ownerId
          and p.ownerType = t.ownerType
          and p.projectId = t.projectId

        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

        left outer join tbl_FSPro_Members d with (nolock)
           on d.fspro_userId = t.dispositionUser
           and d.ownerId > 0

        left outer join usersWatchList wl with (nolock)
          on wl.ownerId = t.ownerId
          and wl.ownerType = t.ownerType
          and wl.taskId = t.taskId

        where t.ownerId = @ownerId
        and t.ownerType = @ownerType
        

    )

    


, tasks_mappedData as (

	select *
		
	  from tasks

)

, tasks_preData as (

	
	select *
	  from tasks_mappedData with (nolock)

	
	
	where 

	(
	

	(
	

	(
	

	(
	[requestedBy]
							= '5266075' 	
	)
or 

	(
	[assignedTo]
							= '5266075' 	
	)
	
	)
	
	)
and 

	(
	[statusDisplay]
							= 'Cancelled' 	
	)
	
	)



	)

	, tasks_data as (

		select * 
		        
		  from tasks_preData
		  

	)



	, tasks_return as (
		select * from tasks_data
		where 1 = 1


	order by dueDate  desc
		
		
		 offset 0 rows fetch next 50 rows only
	
	)

	
	, tasks_result as (
		select ( select count(*) from tasks_data ) as totalCount, 
			   
		       ( select * from tasks_return for json auto, include_null_values ) as data
		)


	select * from tasks_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER


{ts '2025-05-28 14:33:27'} SQL
declare @ownerId int = 1859 ;
    declare @ownerType varchar(3) = 'BRO' ;
    declare @userId int = 0 ;
    declare @today datetime = '2025-05-28' ;
    declare @currentUserId int = 5266075 ;


    with usersWatchList as (
        select distinct wl.ownerId, wl.ownerType, wl.subjectId as taskId
          from tbl_CRM_WatchList wl with (nolock)
         where ownerId = @ownerId
           and ownerType = @ownerType
           and subjectType = 'WRT'
           and active = 'Y'
           and fspro_userId = @currentUserId
    ),

    tasks as (

        select t.taskId,
            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,

            case when t.disposition = 'X' then 'Complete'
                 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.dispositionUser,
            d.firstName + ' ' + d.lastName as dispositionUserFullName,
            t.dispositionDate,
            t.reminderDays,
            t.requestedBy,
            r.firstName as requestedByFirstName,
            r.lastName as requestedByLastName,
            r.firstName + ' ' + r.lastName as requestedByFullName,
            t.assignedTo,
            a.firstName as assignedToFirstName,
            a.lastName as assignedToLastName,
            a.firstName + ' ' + a.lastName as assignedToFullName,
            t.dueDate,
            t.dueDateNotification,
            cast( case when t.disposition = '?' and t.dueDate < @today then 1 else 0 end as bit ) as pastDue,
            t.partnerId,
            t.partnerType,
            t.partnerContactId,
            case when t.partnerId is null then null
                else json_query( '{ "partnerType" : "' + coalesce( t.partnerType, '') + '", "partnerId":' + cast( t.partnerId as varchar ) + ', "contactId" : ' + case when nullif( t.partnerContactId, 0 ) is null then 'null' else cast( t.partnerContactId as varchar ) end + ' }' )
            end as connectedTo,
            t.reminderSent,
            t.taskComment,
            t.taskTemplateId,
            t.workflowRequestId,
            json_query( coalesce( t.jsonData, '{}' )) as jsonData,
            t.acknowledged,
            t.taskTypeId,
            p.projectId,
            p.projectName,
            t.repeating,
            t.frequency,
            coalesce( t.repeatFrom, t.dueDate ) as repeatFrom,
            t.repeatUntil,
            t.sourceTaskId,
            cast( case when wl.taskId is not null then 1 else 0 end as bit ) watching

            

        from tbl_WRK_Tasks t with (nolock)

        left outer join tbl_WRK_Projects p with (nolock)
          on p.ownerId = t.ownerId
          and p.ownerType = t.ownerType
          and p.projectId = t.projectId

        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

        left outer join tbl_FSPro_Members d with (nolock)
           on d.fspro_userId = t.dispositionUser
           and d.ownerId > 0

        left outer join usersWatchList wl with (nolock)
          on wl.ownerId = t.ownerId
          and wl.ownerType = t.ownerType
          and wl.taskId = t.taskId

        where t.ownerId = @ownerId
        and t.ownerType = @ownerType
        

    )

    


, tasks_mappedData as (

	select *
		
	  from tasks

)

, tasks_preData as (

	
	select *
	  from tasks_mappedData with (nolock)

	
	
	where 

	(
	

	(
	

	(
	

	(
	[requestedBy]
							= '5266075' 	
	)
or 

	(
	[assignedTo]
							= '5266075' 	
	)
	
	)
	
	)
and 

	(
	

	(
	[statusDisplay]
							= 'Open' 	
	)
or 

	(
	[statusDisplay]
							= 'Past Due' 	
	)
	
	)
	
	)



	)

	, tasks_data as (

		select * 
		        
		  from tasks_preData
		  

	)



	, tasks_return as (
		select * from tasks_data
		where 1 = 1


	order by dueDate  desc
		
		
		 offset 0 rows fetch next 50 rows only
	
	)

	
	, tasks_result as (
		select ( select count(*) from tasks_data ) as totalCount, 
			   
		       ( select * from tasks_return for json auto, include_null_values ) as data
		)


	select * from tasks_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2025-05-28 14:33:27'} SQL
{ts '2025-05-28 14:33:27'} SQL
declare @ownerId int = 1859 ;
    declare @ownerType varchar(3) = 'BRO' ;
    declare @userId int = 0 ;
    declare @today datetime = '2025-05-28' ;
    declare @currentUserId int = 5266075 ;


    with usersWatchList as (
        select distinct wl.ownerId, wl.ownerType, wl.subjectId as taskId
          from tbl_CRM_WatchList wl with (nolock)
         where ownerId = @ownerId
           and ownerType = @ownerType
           and subjectType = 'WRT'
           and active = 'Y'
           and fspro_userId = @currentUserId
    ),

    tasks as (

        select t.taskId,
            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,

            case when t.disposition = 'X' then 'Complete'
                 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.dispositionUser,
            d.firstName + ' ' + d.lastName as dispositionUserFullName,
            t.dispositionDate,
            t.reminderDays,
            t.requestedBy,
            r.firstName as requestedByFirstName,
            r.lastName as requestedByLastName,
            r.firstName + ' ' + r.lastName as requestedByFullName,
            t.assignedTo,
            a.firstName as assignedToFirstName,
            a.lastName as assignedToLastName,
            a.firstName + ' ' + a.lastName as assignedToFullName,
            t.dueDate,
            t.dueDateNotification,
            cast( case when t.disposition = '?' and t.dueDate < @today then 1 else 0 end as bit ) as pastDue,
            t.partnerId,
            t.partnerType,
            t.partnerContactId,
            case when t.partnerId is null then null
                else json_query( '{ "partnerType" : "' + coalesce( t.partnerType, '') + '", "partnerId":' + cast( t.partnerId as varchar ) + ', "contactId" : ' + case when nullif( t.partnerContactId, 0 ) is null then 'null' else cast( t.partnerContactId as varchar ) end + ' }' )
            end as connectedTo,
            t.reminderSent,
            t.taskComment,
            t.taskTemplateId,
            t.workflowRequestId,
            json_query( coalesce( t.jsonData, '{}' )) as jsonData,
            t.acknowledged,
            t.taskTypeId,
            p.projectId,
            p.projectName,
            t.repeating,
            t.frequency,
            coalesce( t.repeatFrom, t.dueDate ) as repeatFrom,
            t.repeatUntil,
            t.sourceTaskId,
            cast( case when wl.taskId is not null then 1 else 0 end as bit ) watching

            

        from tbl_WRK_Tasks t with (nolock)

        left outer join tbl_WRK_Projects p with (nolock)
          on p.ownerId = t.ownerId
          and p.ownerType = t.ownerType
          and p.projectId = t.projectId

        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

        left outer join tbl_FSPro_Members d with (nolock)
           on d.fspro_userId = t.dispositionUser
           and d.ownerId > 0

        left outer join usersWatchList wl with (nolock)
          on wl.ownerId = t.ownerId
          and wl.ownerType = t.ownerType
          and wl.taskId = t.taskId

        where t.ownerId = @ownerId
        and t.ownerType = @ownerType
        

    )

    


, tasks_mappedData as (

	select *
		
	  from tasks

)

, tasks_preData as (

	
	select *
	  from tasks_mappedData with (nolock)

	
	
	where 

	(
	

	(
	

	(
	

	(
	[requestedBy]
							= '5266075' 	
	)
or 

	(
	[assignedTo]
							= '5266075' 	
	)
	
	)
	
	)
and 

	(
	[statusDisplay]
							= 'On Hold' 	
	)
	
	)



	)

	, tasks_data as (

		select * 
		        
		  from tasks_preData
		  

	)



	, tasks_return as (
		select * from tasks_data
		where 1 = 1


	order by dueDate  desc
		
		
		 offset 0 rows fetch next 50 rows only
	
	)

	
	, tasks_result as (
		select ( select count(*) from tasks_data ) as totalCount, 
			   
		       ( select * from tasks_return for json auto, include_null_values ) as data
		)


	select * from tasks_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

declare @ownerId int = 1859 ;
    declare @ownerType varchar(3) = 'BRO' ;
    declare @userId int = 0 ;
    declare @today datetime = '2025-05-28' ;
    declare @currentUserId int = 5266075 ;


    with usersWatchList as (
        select distinct wl.ownerId, wl.ownerType, wl.subjectId as taskId
          from tbl_CRM_WatchList wl with (nolock)
         where ownerId = @ownerId
           and ownerType = @ownerType
           and subjectType = 'WRT'
           and active = 'Y'
           and fspro_userId = @currentUserId
    ),

    tasks as (

        select t.taskId,
            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,

            case when t.disposition = 'X' then 'Complete'
                 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.dispositionUser,
            d.firstName + ' ' + d.lastName as dispositionUserFullName,
            t.dispositionDate,
            t.reminderDays,
            t.requestedBy,
            r.firstName as requestedByFirstName,
            r.lastName as requestedByLastName,
            r.firstName + ' ' + r.lastName as requestedByFullName,
            t.assignedTo,
            a.firstName as assignedToFirstName,
            a.lastName as assignedToLastName,
            a.firstName + ' ' + a.lastName as assignedToFullName,
            t.dueDate,
            t.dueDateNotification,
            cast( case when t.disposition = '?' and t.dueDate < @today then 1 else 0 end as bit ) as pastDue,
            t.partnerId,
            t.partnerType,
            t.partnerContactId,
            case when t.partnerId is null then null
                else json_query( '{ "partnerType" : "' + coalesce( t.partnerType, '') + '", "partnerId":' + cast( t.partnerId as varchar ) + ', "contactId" : ' + case when nullif( t.partnerContactId, 0 ) is null then 'null' else cast( t.partnerContactId as varchar ) end + ' }' )
            end as connectedTo,
            t.reminderSent,
            t.taskComment,
            t.taskTemplateId,
            t.workflowRequestId,
            json_query( coalesce( t.jsonData, '{}' )) as jsonData,
            t.acknowledged,
            t.taskTypeId,
            p.projectId,
            p.projectName,
            t.repeating,
            t.frequency,
            coalesce( t.repeatFrom, t.dueDate ) as repeatFrom,
            t.repeatUntil,
            t.sourceTaskId,
            cast( case when wl.taskId is not null then 1 else 0 end as bit ) watching

            

        from tbl_WRK_Tasks t with (nolock)

        left outer join tbl_WRK_Projects p with (nolock)
          on p.ownerId = t.ownerId
          and p.ownerType = t.ownerType
          and p.projectId = t.projectId

        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

        left outer join tbl_FSPro_Members d with (nolock)
           on d.fspro_userId = t.dispositionUser
           and d.ownerId > 0

        left outer join usersWatchList wl with (nolock)
          on wl.ownerId = t.ownerId
          and wl.ownerType = t.ownerType
          and wl.taskId = t.taskId

        where t.ownerId = @ownerId
        and t.ownerType = @ownerType
        

    )

    


, tasks_mappedData as (

	select *
		
	  from tasks

)

, tasks_preData as (

	
	select *
	  from tasks_mappedData with (nolock)

	
	
	where 

	(
	

	(
	

	(
	

	(
	[requestedBy]
							= '5266075' 	
	)
or 

	(
	[assignedTo]
							= '5266075' 	
	)
	
	)
	
	)
and 

	(
	[statusDisplay]
							= 'Past Due' 	
	)
	
	)



	)

	, tasks_data as (

		select * 
		        
		  from tasks_preData
		  

	)



	, tasks_return as (
		select * from tasks_data
		where 1 = 1


	order by dueDate  desc
		
		
		 offset 0 rows fetch next 50 rows only
	
	)

	
	, tasks_result as (
		select ( select count(*) from tasks_data ) as totalCount, 
			   
		       ( select * from tasks_return for json auto, include_null_values ) as data
		)


	select * from tasks_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER