taskStatusSummary log
{ts '2024-11-26 08:51:55'} SQL
declare @ownerId int = 387 ;
    declare @ownerType varchar(3) = 'BRO' ;
    declare @userId int = 0 ;
    declare @today datetime = '2024-11-26' ;

    with 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 '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.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.dueDate >= ?
            and t.dueDate <= ? 

    ),

dispositions as (


select distinct disposition ,
  case when disposition = 'X' then 'Completed'
             when disposition = 'Z' then 'Cancelled'
             when disposition = 'H' then 'On Hold'
             when disposition = '?' then
                case when dueDate is null then 'Open'
                    when dueDate < @today then 'Past Due'
                    else 'Open' 
                end
            else disposition end as display
from tbl_WRK_Tasks with (nolock)
where ownerId = @ownerId
and ownerType = @ownerType

),

tasksStatus as (

select d.display , count(*) as taskNumbers
from dispositions d
left outer join tasks t
on t.disposition = d.disposition
group by d.display
having  count(*) >= 0

)

    


, tasksStatus_mappedData as (

	select *
		
	  from tasksStatus

)

, tasksStatus_preData as (

	
	select *
	  from tasksStatus_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, tasksStatus_data as (

		select * 
		        
		  from tasksStatus_preData
		  

	)



	, tasksStatus_return as (
		select * from tasksStatus_data
		where 1 = 1


	order by display
		
		
		 offset 0 rows fetch next 20 rows only
	
	)

	
	, tasksStatus_result as (
		select ( select count(*) from tasksStatus_data ) as totalCount, 
			   
		       ( select * from tasksStatus_return for json auto, include_null_values ) as data
		)


	select * from tasksStatus_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
-- 2024-01-01
-- 2024-12-31

{ts '2024-11-26 10:02:26'} SQL
declare @ownerId int = 387 ;
    declare @ownerType varchar(3) = 'BRO' ;
    declare @userId int = 0 ;
    declare @today datetime = '2024-11-26' ;

    with 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 '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.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.dueDate >= ?
            and t.dueDate <= ? 

    ),

dispositions as (


select distinct disposition ,
  case when disposition = 'X' then 'Completed'
             when disposition = 'Z' then 'Cancelled'
             when disposition = 'H' then 'On Hold'
             when disposition = '?' then
                case when dueDate is null then 'Open'
                    when dueDate < @today then 'Past Due'
                    else 'Open' 
                end
            else disposition end as display
from tbl_WRK_Tasks with (nolock)
where ownerId = @ownerId
and ownerType = @ownerType

),

tasksStatus as (

select d.display , count(*) as taskNumbers
from dispositions d
left outer join tasks t
on t.disposition = d.disposition
group by d.display
having  count(*) >= 0

)

    


, tasksStatus_mappedData as (

	select *
		
	  from tasksStatus

)

, tasksStatus_preData as (

	
	select *
	  from tasksStatus_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, tasksStatus_data as (

		select * 
		        
		  from tasksStatus_preData
		  

	)



	, tasksStatus_return as (
		select * from tasksStatus_data
		where 1 = 1


	order by display
		
		
		 offset 0 rows fetch next 20 rows only
	
	)

	
	, tasksStatus_result as (
		select ( select count(*) from tasksStatus_data ) as totalCount, 
			   
		       ( select * from tasksStatus_return for json auto, include_null_values ) as data
		)


	select * from tasksStatus_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
-- 2024-01-01
-- 2024-12-31

{ts '2024-11-26 12:11:21'} SQL
declare @ownerId int = 387 ;
    declare @ownerType varchar(3) = 'BRO' ;
    declare @userId int = 0 ;
    declare @today datetime = '2024-11-26' ;

    with 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 '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.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.dueDate >= ?
            and t.dueDate <= ? 

    ),

dispositions as (


select distinct disposition ,
  case when disposition = 'X' then 'Completed'
             when disposition = 'Z' then 'Cancelled'
             when disposition = 'H' then 'On Hold'
             when disposition = '?' then
                case when dueDate is null then 'Open'
                    when dueDate < @today then 'Past Due'
                    else 'Open' 
                end
            else disposition end as display
from tbl_WRK_Tasks with (nolock)
where ownerId = @ownerId
and ownerType = @ownerType

),

tasksStatus as (

select d.display , count(*) as taskNumbers
from dispositions d
left outer join tasks t
on t.disposition = d.disposition
group by d.display
having  count(*) >= 0

)

    


, tasksStatus_mappedData as (

	select *
		
	  from tasksStatus

)

, tasksStatus_preData as (

	
	select *
	  from tasksStatus_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, tasksStatus_data as (

		select * 
		        
		  from tasksStatus_preData
		  

	)



	, tasksStatus_return as (
		select * from tasksStatus_data
		where 1 = 1


	order by display
		
		
		 offset 0 rows fetch next 20 rows only
	
	)

	
	, tasksStatus_result as (
		select ( select count(*) from tasksStatus_data ) as totalCount, 
			   
		       ( select * from tasksStatus_return for json auto, include_null_values ) as data
		)


	select * from tasksStatus_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
-- 2024-01-01
-- 2024-12-31

{ts '2024-11-26 12:11:54'} SQL
declare @ownerId int = 387 ;
    declare @ownerType varchar(3) = 'BRO' ;
    declare @userId int = 0 ;
    declare @today datetime = '2024-11-26' ;

    with 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 '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.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.dueDate >= ?
            and t.dueDate <= ? 

    ),

dispositions as (


select distinct disposition ,
  case when disposition = 'X' then 'Completed'
             when disposition = 'Z' then 'Cancelled'
             when disposition = 'H' then 'On Hold'
             when disposition = '?' then
                case when dueDate is null then 'Open'
                    when dueDate < @today then 'Past Due'
                    else 'Open' 
                end
            else disposition end as display
from tbl_WRK_Tasks with (nolock)
where ownerId = @ownerId
and ownerType = @ownerType

),

tasksStatus as (

select d.display , count(*) as taskNumbers
from dispositions d
left outer join tasks t
on t.disposition = d.disposition
group by d.display
having  count(*) >= 0

)

    


, tasksStatus_mappedData as (

	select *
		
	  from tasksStatus

)

, tasksStatus_preData as (

	
	select *
	  from tasksStatus_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, tasksStatus_data as (

		select * 
		        
		  from tasksStatus_preData
		  

	)



	, tasksStatus_return as (
		select * from tasksStatus_data
		where 1 = 1


	order by display
		
		
		 offset 0 rows fetch next 20 rows only
	
	)

	
	, tasksStatus_result as (
		select ( select count(*) from tasksStatus_data ) as totalCount, 
			   
		       ( select * from tasksStatus_return for json auto, include_null_values ) as data
		)


	select * from tasksStatus_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
-- 2024-01-01
-- 2024-12-31

{ts '2024-11-26 12:12:46'} SQL
declare @ownerId int = 387 ;
    declare @ownerType varchar(3) = 'BRO' ;
    declare @userId int = 0 ;
    declare @today datetime = '2024-11-26' ;

    with 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 '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.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.dueDate >= ?
            and t.dueDate <= ? 

    ),

dispositions as (


select distinct disposition ,
  case when disposition = 'X' then 'Completed'
             when disposition = 'Z' then 'Cancelled'
             when disposition = 'H' then 'On Hold'
             when disposition = '?' then
                case when dueDate is null then 'Open'
                    when dueDate < @today then 'Past Due'
                    else 'Open' 
                end
            else disposition end as display
from tbl_WRK_Tasks with (nolock)
where ownerId = @ownerId
and ownerType = @ownerType

),

tasksStatus as (

select d.display , count(*) as taskNumbers
from dispositions d
left outer join tasks t
on t.disposition = d.disposition
group by d.display
having  count(*) >= 0

)

    


, tasksStatus_mappedData as (

	select *
		
	  from tasksStatus

)

, tasksStatus_preData as (

	
	select *
	  from tasksStatus_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, tasksStatus_data as (

		select * 
		        
		  from tasksStatus_preData
		  

	)



	, tasksStatus_return as (
		select * from tasksStatus_data
		where 1 = 1


	order by display
		
		
		 offset 0 rows fetch next 20 rows only
	
	)

	
	, tasksStatus_result as (
		select ( select count(*) from tasksStatus_data ) as totalCount, 
			   
		       ( select * from tasksStatus_return for json auto, include_null_values ) as data
		)


	select * from tasksStatus_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
-- 2024-01-01
-- 2024-12-31