SQL Function to Calculate WI Pending Time from ServiceManager DB

James_Johnson

 I created a sql function that can calculate this value for a work item.

USE [ServiceManager]
GO
/****** Object: UserDefinedFunction [dbo].[GetPendingTime]  Script Date: 6/30/2021 2:22:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetPendingTime](@baseId Nvarchar(50)) RETURNS float

AS
BEGIN
  DECLARE @time float

  set @time = 0

  Declare @Data TABLE(pre_stats nvarchar(max), post_status nvarchar(max), etlID integer primary key, timeadded datetime, endPending bit)

insert into @Data
SELECT
	string.DisplayName
	,string2.DisplayName
	,etl.EntityTransactionLogId
	,etl.TimeAdded
	,endPending = case 
		when
		(
			Pre_Status_785407A9_729D_3A74_A383_575DB0CD50ED = 'B6679968-E84E-96FA-1FEC-8CD4AB39C3DE' and Post_Status_785407A9_729D_3A74_A383_575DB0CD50ED != 'B6679968-E84E-96FA-1FEC-8CD4AB39C3DE'
		)
		then
			1
		else 0
		end
	

from [ServiceManager].[dbo].[MT_System$WorkItem$Incident_Log] il
join DisplayStringView string on string.MPElementId = il.Pre_Status_785407A9_729D_3A74_A383_575DB0CD50ED and string.LanguageCode = 'ENU'
join DisplayStringView string2 on string2.MPElementId = il.Post_Status_785407A9_729D_3A74_A383_575DB0CD50ED and string2.LanguageCode = 'ENU'
join EntityChangeLog ecl on ecl.EntityChangeLogId = il.EntityChangeLogId
join EntityTransactionLog etl on etl.EntityTransactionLogId = ecl.EntityTransactionLogId

where BaseManagedEntityId = @baseId and (Pre_Status_785407A9_729D_3A74_A383_575DB0CD50ED = 'B6679968-E84E-96FA-1FEC-8CD4AB39C3DE' or Post_Status_785407A9_729D_3A74_A383_575DB0CD50ED = 'B6679968-E84E-96FA-1FEC-8CD4AB39C3DE')
order by etl.EntityTransactionLogId asc

set @time = (select sum(isnull(datediff(mi, x.timeadded, d.timeadded), 0)) 
from @Data d 
	outer apply(
		select top 1 timeadded from @Data t2
		where t2.etlID < d.etlID order by t2.etlID desc
	) as x
where d.endPending = 1)

  RETURN @time
END

Then this can be called in any reports just by passing in the BaseManagmentEntityID and it will return in minutes how long a WI has spent pending.

Example: Calculating how long it took to resolve an incident minus the pending time based on ServiceManager database -

DATEDIFF(mi, i.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688, i.ResolvedDate_D2A4C73F_01B8_29C5_895B_5BE4C3DFAC4E) - ISNULL(servicemanager.dbo.GetPendingTime(i.BaseManagedEntityId), 0)