Home Analyst Portal

Resolution rate percent

Magnus_Lundgren1Magnus_Lundgren1 Customer Adept IT Monkey ✭✭
Ive gotten a question about a report that displays in percent how many incidents are solved within 3 days not included weekends displayed month by month, im not an sql wiz so im strugling with this.
And we dont have any SLOs so i cant use that.

Answers

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    Here you go
    Just Use this for SSRS to create the desired chart, pull out what you do not need. (like the dates)
    The Key to this table is the
    
    ,datediff(dd, WIM.CreatedDate, idm.ResolvedDate) +
    	case when datepart(dw, WIM.CreatedDate) = 7 then 1 else 0 end - (datediff(wk, WIM.CreatedDate, idm.ResolvedDate) * 2)
    	case when datepart(dw, WIM.CreatedDate) = 1 then 1 else 0 end +
    	case when datepart(dw, idm.ResolvedDate) = 1 then 1 else 0 end As DaystoResolve
    

    What this will run is a calculation between Create Date and Resolved Date Counting the days Mon - Fri. It will consider holidays as a working day.
    DECLARE @StartDate datetime, @EndDate  datetime
    
    		SET @StartDate = '01/01/2016'
    		SET @EndDate = '01/01/2017'
    
    SELECT DISTINCT WIM.Id AS Incident
    				,ISV.IncidentStatusValue AS IncidentStatus
    				,WIM.Title AS Title
    				,WIM.CreatedDate AS CreatedDate
    				,(SELECT MAX(v) FROM (VALUES(MAX(WLD.EnteredDate)),(MAX(WIAC.EnteredDate))) as value(v)) AS LastModified
    				,IDM.ResolvedDate AS ResolvedDate
    				,IDM.ResolutionDescription
    				,ITQ.IncidentTierQueuesValue
    				,AffectedUser.Department
    				,IDM.Priority
    				,datediff(dd, WIM.CreatedDate, idm.ResolvedDate) +
    						case when datepart(dw, WIM.CreatedDate) = 7 then 1 else 0 end - (datediff(wk, WIM.CreatedDate, idm.ResolvedDate) * 2)
    						case when datepart(dw, WIM.CreatedDate) = 1 then 1 else 0 end +
    						case when datepart(dw, idm.ResolvedDate) = 1 then 1 else 0 end As DaystoResolve
    
    FROM [SC2012_DWDataMart].[dbo].[WorkItemDimvw] WIM
    		left outer join [SC2012_DWDataMart].[dbo].[IncidentDimvw]
    		IDM on IDM.Id = WIM.Id
    		left join [SC2012_DWDataMart].[dbo].[WorkItemCreatedByUserFactvw]		WICB on WICB.WorkItemDimKey = WIM.WorkItemDimKey
    		left join [SC2012_DWDataMart].[dbo].[UserDimvw]						CreatedBy on CreatedBy.UserDimKey = WICB.WorkItemCreatedByUser_UserDimKey
    		Left join [SC2012_DWDataMart].[dbo].[IncidentStatusvw]					ISV ON ISV.IncidentStatusId = IDM.Status_IncidentStatusId
    		left join [SC2012_DWDataMart].[dbo].[WorkItemAboutConfigItemFactvw]	WCI on WIM.WorkItemDimKey = WCI.WorkItemDimKey
    		left join [SC2012_DWDataMart].[dbo].[ConfigItemDimvw]					CID on CID.ConfigItemDimKey = WCI.WorkItemAboutConfigItem_ConfigItemDimKey
    		left join [SC2012_DWDataMart].[dbo].[IncidentStatusDurationFactvw]		TimeWorked on WIM.[WorkItemDimKey] = Timeworked.[IncidentDimKey]
    		left join [SC2012_DWDataMart].[dbo].[WorkItemAssignedToUserFactvw]		WIAA on WIM.WorkItemDimKey = WIAA.WorkItemDimKey AND WIAA.DeletedDate IS NULL
    		left join [SC2012_DWDataMart].[dbo].[UserDimvw]						AssignedTo on AssignedTo.UserDimKey = WIAA.WorkItemAssignedToUser_UserDimKey
    		left join [SC2012_DWDataMart].[dbo].[WorkItemAffectedUserFactvw]		WIAU on WIM.WorkItemDimKey = WIAU.WorkItemDimKey
    		left join [SC2012_DWDataMart].[dbo].[UserDimvw]						AffectedUser on AffectedUser.UserDimKey = WIAU.[WorkItemAffectedUser_UserDimKey]
    		left join [SC2012_DWDataMart].[dbo].[IncidentTierQueues]				ITQ on IDM.Tierqueue = ITQ.[ID]
    		left join [SC2012_DWDataMart].[dbo].[IncidentRelatesToActionLogFactvw] ItoALF on ItoALF.IncidentDimKey = IDM.IncidentDimKey
    		left join [SC2012_DWDataMart].[dbo].[WorkItemActionLogDimvw]			WLD on ItoALF.TroubleTicketHasActionLog_WorkItemActionLogDimKey = WLD.WorkItemActionLogDimKey
    		left join [SC2012_DWDataMart].[dbo].[IncidentRelatesToAnalystCommentLogFactvw] ItoAC on IDM.IncidentDimKey = ItoAC.IncidentDimKey
    		left join [SC2012_DWDataMart].[dbo].[WorkItemAnalystCommentLogDimvw]	WIAC on WIAC.WorkItemAnalystCommentLogDimKey = ItoAC.TroubleTicketHasAnalystComment_WorkItemAnalystCommentLogDimKey
    		WHERE ITQ.IncidentTierQueuesValue = 'Your Support Group'
    		and WIM.Id like 'IR%'
    		AND WIM.CreatedDate >= @StartDate AND WIM.CreatedDate <= @EndDate
    				group by
    				WIM.Id,
    				ISV.IncidentStatusValue,
    				WIM.Title,
    				WIM.CreatedDate,
    				IDM.ResolvedDate,
    				IDM.ResolutionDescription,
    			 	ITQ.IncidentTierQueuesValue,<
    				AffectedUser.Department,
    				IDM.Priority
    		ORDER BY IncidentStatus, Incident DESC
  • Magnus_Lundgren1Magnus_Lundgren1 Customer Adept IT Monkey ✭✭
    Sorry but i cant get the query to work, getting an error on the second case. and alot of multipart identifiers cant be found.
    i changed the SC2012_DWDataMart as ours is just named DWDataMart.
  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    Woops just noticed my code post update there is a misplaced braket. 
    Attached is a txt. Yes just update the SC2012_DWDatmart to you enviroment.
  • Magnus_Lundgren1Magnus_Lundgren1 Customer Adept IT Monkey ✭✭
    Thanks got it working,
    But i cant seem to get the date selection to work. it gets all incidents even if i select a from and to date.
    i tried US format and Swedish format. "2016-10-01"
  • Magnus_Lundgren1Magnus_Lundgren1 Customer Adept IT Monkey ✭✭
    Sorry my brain did not work this morning i commented out WHERE becouse i wanted all support groups, took me a while to see the and startdate enddate thingy.

    Thanks you soo much for this!
Sign In or Register to comment.