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
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)