Modify the "Get Report Data" action in Internal - Work Queues Object

[Asilarow]

Currently the "Get report Data" action allows for retrieving most of the items from the queue, however it proves problematic for providing reporting on items that have had more than one attempt.

The Action has an input flag of "Treat each attempt separately", but changing it, only changes the way the output value for "item count" is set, and does not actually provide information from the queue for each attempt separately.
The Item Ids output collection will contain only 1 row instead of as many rows as there were attempts.

This is all due to the SQL statement being used by this action.
In the FROM clause, when selecting the ouput fields for the @individuals table, it uses the DISTINCT statement.

Example below:

exec sp_executesql N' declare @individuals table ( ident bigint not null primary key, id uniqueidentifier not null, state tinyint not null, createdate datetime not null, lastupdateddate datetime not null, finishdate datetime null, worktime int not null default 0 ); insert into @individuals select i.ident, i.id, i.state, i.loaded, i.lastupdated, i.finished, isnull(i.attemptworktime,0) from BPVWorkQueueItem i join BPAWorkQueue q on i.queueid = q.id left join BPVSession s on i.sessionid = s.sessionid left join BPAResource r on r.resourceid = s.runningresourceid where q.name = @queuename and i.finished between @startdate_fin and @enddate_fin and i.state in (@state1); select count(*) as "itemcount", sum(i.worktime) as "total", min(i.worktime) as "minimum", max(i.worktime) as "maximum", avg(i.worktime) as "mean" from @individuals i; select ((i1.median + i2.median) / 2) as "avg-median" from ( select max(i1.worktime) as median from ( select top 50 percent i.worktime from @individuals i order by worktime asc ) i1 ) i1 cross join ( select min(i2.worktime) as median from ( select top 50 percent i.worktime from @individuals i order by worktime desc ) i2 ) i2; select distinct i.id from @individuals i;',N'@queuename nvarchar(25),@startdate_fin datetime,@enddate_fin datetime,@state1 int',@queuename=N'QueueName',@startdate_fin='2022-03-02 00:00:00',@enddate_fin='2022-03-02 23:00:00',@state1=5

Instead, when the "Treat each attempt separately" flag is set to true, this should provide all ids found, plus additional information such as attempt number, worktime, and last update time.

This way, the report data will be much more meaningful, as it will provide insight into how long each separate attempt took, as well as provide the means to calculate when was each attempt started.

Example modified query below:

exec sp_executesql N' declare @individuals table ( ident bigint not null primary key, id uniqueidentifier not null, state tinyint not null, createdate datetime not null, lastupdateddate datetime not null, finishdate datetime null, worktime int not null default 0); insert into @individuals select i.ident, i.id, i.state, i.loaded, i.lastupdated, i.finished, isnull(i.attemptworktime,0) from BPVWorkQueueItem i join BPAWorkQueue q on i.queueid = q.id left join BPVSession s on i.sessionid = s.sessionid left join BPAResource r on r.resourceid = s.runningresourceid where q.name = @queuename and i.finished between @startdate_fin and @enddate_fin and i.state in (@state1); select count(*) as "itemcount", sum(i.worktime) as "total", min(i.worktime) as "minimum", max(i.worktime) as "maximum", avg(i.worktime) as "mean" from @individuals i; select ((i1.median + i2.median) / 2) as "avg-median" from ( select max(i1.worktime) as median from ( select top 50 percent i.worktime from @individuals i order by worktime asc ) i1 ) i1 cross join ( select min(i2.worktime) as median from ( select top 50 percent i.worktime from @individuals i order by worktime desc ) i2 ) i2; select i.id, i.ident, i.worktime, i.lastupdateddate from @individuals i;',N'@queuename nvarchar(25),@startdate_fin datetime,@enddate_fin datetime,@state1 int',@queuename=N'QueueName',@startdate_fin='2022-03-02 00:00:00',@enddate_fin='2022-03-02 23:00:00',@state1=5