-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'ServiceManager_Report_IncidentManagement_SP_IncidentAnalystGetData'
)
DROP PROCEDURE dbo.ServiceManager_Report_IncidentManagement_SP_IncidentAnalystGetData
GO
CREATE PROCEDURE dbo.ServiceManager_Report_IncidentManagement_SP_IncidentAnalystGetData
@DateFilter nvarchar(256) = null,
@StartDate datetime,
@EndDate datetime,
@Analyst nvarchar(max) = '0',
@Classification nvarchar(max) = '-1',
@SupportGroup nvarchar(max) = '-1',
@Queue nvarchar(max) = '-1',
@Service nvarchar(max) = '-1'
AS
BEGIN
SET NOCOUNT ON
I.IncidentDimKey,
I.EntityDimKey,
WI.WorkItemDimKey,
I.CreatedDate,
IsActive = CASE WHEN(
(
I.Status <> 'IncidentStatusEnum.Resolved' AND
I.Status <> 'IncidentStatusEnum.Closed'
) OR
I.ResolvedDate IS NULL
)
THEN 1
ELSE NULL
END,
IsResolved = CASE WHEN
(
(
I.Status = 'IncidentStatusEnum.Resolved'
) AND
I.ResolvedDate IS NOT NULL
)
THEN 1
ELSE NULL
END,
IsClosed = CASE WHEN
(
(
I.Status = 'IncidentStatusEnum.Closed'
) AND
I.ResolvedDate IS NOT NULL
)
THEN 1
ELSE NULL
END,
IsEscalated = NULLIF(I.Escalated, 0),
TTR= CASE
WHEN
(
(
I.Status = 'IncidentStatusEnum.Resolved' OR
I.Status = 'IncidentStatusEnum.Closed'
) AND
I.ResolvedDate IS NOT NULL
)
THEN
DATEDIFF(MINUTE, I.CreatedDate, I.ResolvedDate)
ELSE NULL
END,
NotMeetsSLA = CASE WHEN
(
I.TargetResolutionTime IS NULL
)
OR
(
(
(
I.Status <> 'IncidentStatusEnum.Resolved' AND
I.Status <> 'IncidentStatusEnum.Closed'
) OR
I.ResolvedDate IS NULL
) AND
I.TargetResolutionTime >= GETUTCDATE()
)
THEN NULL
WHEN
(
(
(
I.Status = 'IncidentStatusEnum.Resolved' OR
I.Status = 'IncidentStatusEnum.Closed'
) AND
I.ResolvedDate IS NOT NULL
) AND
I.ResolvedDate <= I.TargetResolutionTime
)
THEN NULL
ELSE 0
END,
AssignedTo = WIATUFct.WorkItemAssignedToUser_UserDimKey,
ResolvedBy = WIRBUFct.TroubleTicketResolvedByUser_UserDimKey
from IncidentDimvw I
INNER JOIN dbo.WorkItemDimvw WI
ON WI.EntityDimKey = I.EntityDimKey
AND I.IsDeleted = 0
LEFT OUTER JOIN
WorkItemAssignedToUserFactvw WIATUFct
ON WI.WorkItemDimKey = WIATUFct.WorkItemDimKey
AND WIATUFct.DeletedDate IS NULL
AND WIATUFct.DateKey >= CONVERT(nvarchar(8), I.CreatedDate, 112)
LEFT OUTER JOIN
IncidentResolvedByUserFactvw WIRBUFct
ON I.IncidentDimKey = WIRBUFct.IncidentDimKey
AND WIRBUFct.DeletedDate IS NULL
AND WIRBUFct.DateKey >= CONVERT(nvarchar(8), I.CreatedDate, 112)
left join
(
select
WIGCWI.WorkItemGroupContainsWorkItems_WorkItemDimKey ContainedWIDimKey,
WIG.DisplayName,
WIG.WorkItemGroupDimKey
from
dbo.WorkItemGroupDimvw WIG
inner join dbo.WorkItemGroupContainsWorkItemFactvw WIGCWI
ON WIGCWI.WorkItemGroupDimKey = WIG.WorkItemGroupDimKey
AND WIGCWI.DeletedDate IS NULL
)WIGroup
ON (WIGroup.ContainedWIDimKey = WI.WorkItemDimKey)
LEFT OUTER JOIN
(
Select
IISFact.WorkItemDimKey,
S.ServiceDimKey,
IISFact.DateKey
from
dbo.WorkItemAboutConfigItemFactvw IISFact
LEFT JOIN dbo.ConfigItemDimvw CI
ON IISFact.WorkItemAboutConfigItem_ConfigItemDimKey = CI.ConfigItemDimKey
LEFT JOIN dbo.ServiceDimvw S
ON CI.EntityDimKey = S.EntityDimKey
WHERE IISFact.DeletedDate IS NULL
AND
((S.ServiceDimKey IN (select value from @tableService)) OR (-1 IN (select value from @tableService)))
)S
ON S.WorkItemDimKey = WI.WorkItemDimKey
AND S.DateKey >= CONVERT(nvarchar(8), I.CreatedDate, 112)
Where
(
(@DateFilter = 'ResolvedOn' AND ((I.ResolvedDate >= @StartDate) AND (I.ResolvedDate < @EndDate)) ) OR
(@DateFilter = 'ClosedOn' AND ((I.ClosedDate >= @StartDate) AND (I.ClosedDate < @EndDate))) OR
(@DateFilter = 'CreatedOn' AND ((I.CreatedDate >= @StartDate) AND (I.CreatedDate < @EndDate)) )
)
AND
(@StartDate <= @EndDate)
AND
(
(I.Classification_IncidentClassificationId IN (Select value From @tableClassification))
OR (-1 IN (Select value From @tableClassification))
)
AND
((I.TierQueue_IncidentTierQueuesId IN (select value from @tableSupportGroup)) OR (-1 IN (select value from @tableSupportGroup)) ) AND
((WIGroup.WorkItemGroupDimKey IN (Select value from @tableQueue)) OR (-1 IN (select value from @tableQueue))) AND
((S.ServiceDimKey IN (select value from @tableService)) OR (-1 IN (select value from @tableService))) AND
(
(0 IN (Select value from @tableAnalyst)) OR
(WIATUFct.WorkItemAssignedToUser_UserDimKey IN (Select value from @tableAnalyst)) OR
(WIRBUFct.TroubleTicketResolvedByUser_UserDimKey IN (Select value from @tableAnalyst))
)
GROUP BY _I.AssignedTo
) AssignedTo
ON AssignedTo.UserDimKey = U.UserDimKey
--ResolvedBy
LEFT OUTER JOIN
(
Select
_I.ResolvedBy UserDimKey,
COUNT(_I.IncidentDimKey) Resolved,
AVG(TTR) AverageTTR
from
@I _I
GROUP BY _I.ResolvedBy
) ResolvedBy
ON ResolvedBy.UserDimKey = U.UserDimKey
--WorkedOn
LEFT OUTER JOIN
(
Select
BTHWUFct.BillableTimeHasWorkingUser_UserDimKey AS UserDimKey,
COUNT(_I.IncidentDimKey) WorkedOnIncidents,
SUM(BT.TimeInMinutes) TotalTimeWorked
from
@I _I
INNER JOIN dbo.WorkItemHasBillableTimeFactvw WIHBTFct
ON _I.WorkItemDimKey = WIHBTFct.WorkItemDimKey
AND WIHBTFct.DeletedDate IS NULL
AND WIHBTFct.DateKey >= CONVERT(nvarchar(8), _I.CreatedDate, 112)
INNER JOIN dbo.BillableTimeDimvw BT
ON BT.BillableTimeDimKey = WIHBTFct.WorkItemHasBillableTime_BillableTimeDimKey
INNER JOIN dbo.BillableTimeHasWorkingUserFactvw BTHWUFct
ON BT.BillableTimeDimKey = BTHWUFct.BillableTimeDimKey
AND BTHWUFct.DeletedDate IS NULL
AND BTHWUFct.DateKey >= CONVERT(nvarchar(8), _I.CreatedDate, 112)
GROUP BY BTHWUFct.BillableTimeHasWorkingUser_UserDimKey
) WorkedBy
ON WorkedBy.UserDimKey = U.UserDimKey
WHERE ((0 in (SELECT value from @tableAnalyst)) OR (U.UserDimKey IN (SELECT value from @tableAnalyst)))
AND Not
(
AssignedTo.UserDimKey IS NULL AND
ResolvedBy.UserDimKey IS NULL AND
WorkedBy.UserDimKey IS NULL
)
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_IncidentManagement_SP_IncidentAnalystGetData TO reportuser
GO
Home