View: V_SumDurationCurrentHour

This view shows the processing time per process step from check-out to check-in and calculates worker performance in the past hour.

Column	Description
DoneAt	The date and time from the DoneAt field (DateOnly in V_Steps).
SumDurationSec	The total number of seconds during which the documents for this step were checked out (sum of CheckOutDuration in V_Steps).
AvgDurationSec	The average number of seconds during which the documents for this process step were checked out (average of CheckOutDuration in V_Steps).
SumDurationHours	The number of hours in which the documents for this step were checked out. (The sum of CheckOutDurationHours in V_Steps.)
AvgDurationHours	The average number of hours during which the documents for this process step were checked out (average CheckOutDurationHours in V_Steps).
Client	The name of the client (Client in V_Steps).
Process	The name of the process (Process in V_Steps).
ProcessStep	The name of the process step (ProcessStep in V_Steps).
DocumentCount	The number of documents (number of DocumentID's in V_Steps).
Action	Create Check out Check In Check in with Error Routed Manually Undo Check out Statistics (Action in V_Steps)
WorkerPerformance	The number of documents divided by the sum of the check-out time in hours – or 0 if the sum of the check-out time in hours is 0 (DocumentID, CheckOutDurationHours in V_Steps).
ServiceType	0 = Activity 1 = Collector 2 = Agent 3 = Global Parameter Set 4 = Client Parameter Set 5 = Process Parameter Set 6 = Interactive 7 = Platform 8 = License 9 = Activity and Parameter Set 10 = Feature Group (ServiceType in V_Steps)

SQL Script

CREATE VIEW  [dbo].[V_SumDurationCurrentHour]
AS
SELECT DoneAt, SUM(CheckOutDuration) AS SumDurationSec, AVG(CheckOutDuration) AS AvgDurationSec, SUM(CheckOutDurationHours) AS SumDurationHours,
	         AVG(CheckOutDurationHours) AS AvgDurationHours, Client, Process, ProcessStep, COUNT(DocumentID) AS DocumentCount, Action,
	         CASE SUM(CheckOutDurationHours) WHEN 0 THEN 0 ELSE COUNT(DocumentID) / SUM(CheckOutDurationHours) END AS WorkerPerformance, ServiceType
FROM dbo.V_Steps
GROUP BY Client, Process, ProcessStep, Action, ServiceType, DateOnly, DoneAt
HAVING (Action = 'CheckIn') AND (DateOnly = CONVERT(datetime, CONVERT(char (10), GETUTCDATE(), 112))) AND (DATEDIFF(second, DoneAt, GETUTCDATE()) <=3600)

Uses

V_Steps

The available tables

The available views