Resolution rate percent

Magnus_Lundgren1

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.

Brian_Wiest

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_Lundgren1

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_Wiest

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_Lundgren1

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_Lundgren1

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!