Query:
Be sure to update the variables on the first few lines of the query below:
- Replace the Staff Tag ID value on the first line with the ID of your staff tag, or update the query to work with whatever method your organization uses for keeping track of staff in Rock.
-
Update the No Contact and In Progress Status ID values with comma separated lists of the corresponding statuses for each of your connection types. It may just be a single number if you only have one connection type.
If any of your connection types don't have statuses that correspond to "No Contact" or "In Progress", most of the report will still work perfectly. Those two columns just won't include any requests of that type.
You can get a list of all possible statuses by running this SQL query: SELECT * FROM ConnectionStatus
- Optional: Change the Idle Days value to adjust how many days without activity are required before a request is considered idle.
DECLARE @StaffTagID AS int = 1
DECLARE @NoContactStatusIDs AS varchar(50) = '1,4'
DECLARE @InProgressStatusIDs AS varchar(50) = '2,5,6,9'
DECLARE @IdleDays AS int = 3
----------------------------------------
SET @ConnectionType = ISNULL(@ConnectionType, 0)
DECLARE @TimeNow AS datetime2 = GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time'
DECLARE @AlertIcon AS varchar(50) = '<i class="fa fa-sm fa-exclamation-triangle"></i> '
DROP TABLE IF EXISTS #NOCONTACTIDS
DROP TABLE IF EXISTS #INPROGRESSIDS
SELECT * INTO #NOCONTACTIDS FROM STRING_SPLIT(@NoContactStatusIDs, ',')
SELECT * INTO #INPROGRESSIDS FROM STRING_SPLIT(@InProgressStatusIDs, ',')
SELECT ID, ISNULL(NickName + ' ' + LastName, '[UNASSIGNED]') AS Connector, IsStaff,
'<span data-sort="' + X.ActiveSort + '" class="badge badge-' + X.ActiveClass + '">' + X.ActiveAlert + CAST(Active AS varchar) + '</span>' AS Active,
'<span data-sort="' + X.FutureSort + '" class="badge badge-' + X.FutureClass + '">' + X.FutureAlert + CAST(Future AS varchar) + '</span>' AS Future,
'<span data-sort="' + X.InProgressSort + '" class="badge badge-' + X.InProgressClass + '">' + X.InProgressAlert + CAST(InProgress AS varchar) + '</span>' AS InProgress,
'<span data-sort="' + X.NoContactSort + '" class="badge badge-' + X.NoContactClass + '">' + X.NoContactAlert + CAST(NoContact AS varchar) + '</span>' AS NoContact,
'<span data-sort="' + X.IdleSort + '" class="badge badge-' + X.IdleClass + '">' + X.IdleAlert + CAST(Idle AS varchar) + '</span>' AS Idle,
'<span data-sort="' + X.CriticalSort + '" class="badge badge-' + X.CriticalClass + '">' + X.CriticalAlert + CAST(Critical AS varchar) + '</span>' AS Critical,
'<span data-sort="' + X.ConnectedSort + '" class="badge badge-' + X.ConnectedClass + '">' + CAST(Connected AS varchar) + '</span>' AS Connected,
'<i data-sort="' + CAST(SeveritySort AS varchar) + '" class="fa fa-lg fa-circle text-' + CASE SeveritySort WHEN 0 THEN 'danger' WHEN 1 THEN 'danger' WHEN 2 THEN 'critical' WHEN 3 THEN 'warning' WHEN 6 THEN 'gray-300' ELSE 'info' END + '"> </span>' AS Severity
FROM (SELECT P.ID, P.LastName, P.NickName, CAST(CASE WHEN T.ID IS NULL THEN 0 ELSE 1 END AS bit) AS IsStaff,
SUM(IIF(R.ConnectionState = 0 OR (R.ConnectionState = 2 AND R.FollowUpDate <= @TimeNow), 1, 0)) AS Active,
SUM(IIF(R.ConnectionState = 2 AND R.FollowUpDate > @TimeNow, 1, 0)) AS Future,
SUM(IIF((R.ConnectionState = 0 OR (R.ConnectionState = 2 AND R.FollowUpDate <= @TimeNow)) AND X2.IsNoContact = 1, 1, 0)) AS NoContact,
SUM(IIF((R.ConnectionState = 0 OR (R.ConnectionState = 2 AND R.FollowUpDate <= @TimeNow)) AND X2.IsInProgress = 1, 1, 0)) AS InProgress,
SUM(IIF((R.ConnectionState = 0 OR (R.ConnectionState = 2 AND R.FollowUpDate <= @TimeNow)) AND DATEDIFF(minute, X2.RecentDate, @TimeNow) / 1440.0 > @IdleDays, 1, 0)) AS Idle,
SUM(IIF((R.ConnectionState = 0 OR (R.ConnectionState = 2 AND R.FollowUpDate <= @TimeNow)) AND (T.ID IS NULL OR (X2.IsNoContact = 1 AND DATEDIFF(minute, X2.RecentDate, @TimeNow) / 1440.0 > @IdleDays)), 1, 0)) AS Critical,
SUM(IIF(R.ConnectionState = 3, 1, 0)) AS Connected
FROM ConnectionRequest R
INNER JOIN ConnectionOpportunity O ON O.ID = R.ConnectionOpportunityID
LEFT JOIN PersonAlias PA ON PA.ID = R.ConnectorPersonAliasID
LEFT JOIN Person P ON P.ID = PA.PersonID
LEFT JOIN TaggedItem T ON T.EntityGUID = P.GUID AND T.TagID = @StaffTagID
OUTER APPLY (SELECT MAX(CreatedDateTime) AS RecentDate, ConnectionRequestID FROM ConnectionRequestActivity WHERE ConnectionRequestID = R.ID GROUP BY ConnectionRequestID) X1
CROSS APPLY
(
SELECT ISNULL(X1.RecentDate, R.CreatedDateTime) AS RecentDate,
CAST(CASE WHEN R.ConnectionStatusID IN (SELECT * FROM #NOCONTACTIDS) THEN 1 ELSE 0 END AS bit) AS IsNoContact,
CAST(CASE WHEN R.ConnectionStatusID IN (SELECT * FROM #INPROGRESSIDS) THEN 1 ELSE 0 END AS bit) AS IsInProgress
) X2
WHERE O.ConnectionTypeID = @ConnectionType OR @ConnectionType = 0
GROUP BY P.ID, P.NickName, P.LastName, T.ID) AS A
CROSS APPLY
(
SELECT
RIGHT('00000' + CAST(Active AS varchar), 5) AS ActiveSort,
CASE WHEN Active > 0 AND IsStaff = 0 THEN 'danger' WHEN Active > 0 THEN 'info' ELSE 'default bg-gray-300' END AS ActiveClass,
IIF(Active > 0 AND IsStaff = 0, @AlertIcon, '') AS ActiveAlert,
RIGHT('00000' + CAST(Future AS varchar), 5) AS FutureSort,
CASE WHEN Future > 0 AND IsStaff = 0 THEN 'warning' WHEN Future > 0 THEN 'default' ELSE 'default bg-gray-300' END AS FutureClass,
IIF(Future > 0 AND IsStaff = 0, @AlertIcon, '') AS FutureAlert,
RIGHT('00000' + CAST(InProgress AS varchar), 5) AS InProgressSort,
CASE WHEN InProgress > 0 AND IsStaff = 0 THEN 'danger' WHEN InProgress > 0 THEN 'default' ELSE 'default bg-gray-300' END AS InProgressClass,
IIF(InProgress > 0 AND IsStaff = 0, @AlertIcon, '') AS InProgressAlert,
RIGHT('00000' + CAST(NoContact AS varchar), 5) AS NoContactSort,
CASE WHEN NoContact > 0 THEN 'danger' ELSE 'default bg-gray-300' END AS NoContactClass,
IIF(NoContact > 0 AND IsStaff = 0, @AlertIcon, '') AS NoContactAlert,
RIGHT('00000' + CAST(Idle AS varchar), 5) AS IdleSort,
CASE WHEN Idle > 0 AND IsStaff = 0 THEN 'danger' WHEN Idle > 0 THEN 'critical' ELSE 'default bg-gray-300' END AS IdleClass,
IIF(Idle > 0 AND IsStaff = 0, @AlertIcon, '') AS IdleAlert,
RIGHT('00000' + CAST(Critical AS varchar), 5) AS CriticalSort,
CASE WHEN Critical > 0 THEN 'danger' ELSE 'default bg-gray-300' END AS CriticalClass,
IIF(Critical > 0, @AlertIcon, '') AS CriticalAlert,
RIGHT('00000' + CAST(Connected AS varchar), 5) AS ConnectedSort,
CASE WHEN Connected > 0 THEN 'success' ELSE 'default bg-gray-300' END AS ConnectedClass,
CASE WHEN IsStaff = 0 AND (Active > 0 OR Future > 0 OR NoContact > 0 OR InProgress > 0 OR Idle > 0) THEN 0
WHEN Critical > 0 THEN 1
WHEN NoContact > 0 THEN 2
WHEN Idle > 0 THEN 3
WHEN Future > 0 OR InProgress > 0 THEN 4
WHEN Active > 0 THEN 5
ELSE 6
END AS SeveritySort
) X
ORDER BY SeveritySort, IsStaff DESC, LastName, NickName
DROP TABLE IF EXISTS #NOCONTACTIDS
DROP TABLE IF EXISTS #INPROGRESSIDS