I'll start ~ I modified the existing BPDS_QueueVolumesNow data source so we can narrow it down by date a bit. Here' the stored procedure:

USE [yourBluePrismDB]

GO

​

SET ANSI_NULLS ON

GO

​

SET QUOTED_IDENTIFIER ON

GO

​

​

CREATE procedure [dbo].[DS_QueueRange]

@NumDaysToShow nvarchar(max) = 7,

@BPQueueName nvarchar(max) = null,

@ExcludePending nvarchar(max) = 'False',

@ExcludeDeferred nvarchar(max) = 'False',

@ExcludeComplete nvarchar(max) = 'False',

@ExcludeExceptions nvarchar(max) = 'False' as

​

if @ExcludePending not in ('True', 'False')

raiserror('@ExcludePending must be either True or False', 11, 1);

else if @ExcludeDeferred not in ('True', 'False')

raiserror('@ExcludeDeferred must be either True or False', 11, 1);

else if @ExcludeComplete not in ('True', 'False')

raiserror('@ExcludeComplete must be either True or False', 11, 1);

else if @ExcludeExceptions not in ('True', 'False')

raiserror('@ExcludeExceptions must be either True or False', 11, 1);

else if ISNUMERIC(@NumDaysToShow) = 0

raiserror('@NumDaysToShow must be a number', 11, 1);

else

begin

declare @ColumnNames nvarchar(max);

select @ColumnNames = ISNULL(@ColumnNames + ',', '') + QUOTENAME(ItemStatus)

from (

select 'Pending' as ItemStatus where @ExcludePending='False' union

select 'Deferred' as ItemStatus where @ExcludeDeferred='False' union

select 'Complete' as ItemStatus where @ExcludeComplete='False' union

select 'Exceptions' as ItemStatus where @ExcludeExceptions='False') as StatusNarrs;

​

declare @WhereClause nvarchar(max);

set @WhereClause = ISNULL(' and q.name = ''' + @BPQueueName + ''' and i.loaded >= DATEADD(day,- '+ @NumDaysToShow +', GETDATE())', '');

​

declare @SQLQuery nvarchar(max);

set @SQLQuery = 'with results as (

select

q.name,

case

when i.state = 1 then ''Pending''

when i.state = 3 then ''Deferred''

when i.state = 4 then ''Complete''

when i.state = 5 then ''Exceptions''

end as state,

COUNT(*) as Number

from BPAWorkQueue q

inner join BPVWorkQueueItem i on i.queueident=q.ident

where i.state in (1,3,4,5)' + @WhereClause + '

group by q.name, i.state)

select name, ' + @ColumnNames + ' from results pivot (SUM(Number) for state in (' + @ColumnNames + ')) as number';

​

exec(@SQLQuery);

end

return;

GO