Home Community Uploads
image

Cireson Partners, Customers and Community members share your customizations and examples here to help benefit the community as a whole to earn Kudos and badges.

DISCLAIMER

All files and projects located here are provided and come "as-is" and without any warranty or support. Use at your own risk. Your use of Community Uploads is subject to our Terms of Use.

Cireson does not and will not support or maintain these enhancements, extensions, and scripts.

For Team Cireson uploads click here.

SQL Function to Calculate WI Pending Time from ServiceManager DB

James_JohnsonJames_Johnson Customer Advanced IT Monkey ✭✭✭

 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)


Sign In or Register to comment.