-- 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_GetIncidentResolutionData'
)
DROP PROCEDURE dbo.ServiceManager_Report_IncidentManagement_GetIncidentResolutionData
GO
CREATE PROCEDURE dbo.ServiceManager_Report_IncidentManagement_GetIncidentResolutionData
@DateFilter nvarchar(256) = 'CreatedOn',
@StartDate datetime,
@EndDate datetime,
@Priority nvarchar(max) = -1,
@Source nvarchar(max) = -1,
@Status nvarchar(max) = -1,
@Impact nvarchar(max) = -1,
@Urgency nvarchar(max) = -1,
@AggregationType nvarchar(64) = 'Weekly'
AS
BEGIN
SET NOCOUNT ON
Select
AggregatedDate=
CASE
WHEN ( @AggregationType = 'Daily')
THEN AggregatedDateDim.DailyAggregate
WHEN ( @AggregationType = 'Weekly')
THEN AggregatedDateDim.WeeklyAggregate
WHEN ( @AggregationType = 'Monthly')
THEN AggregatedDateDim.MonthlyAggregate
WHEN ( @AggregationType = 'Quarterly')
THEN AggregatedDateDim.QuarterlyAggregate
WHEN ( @AggregationType = 'Yearly')
THEN AggregatedDateDim.YearlyAggregate
END,
COUNT(_I.IncidentDimKey) IncidentCount,
MeetsSLA = SUM(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 1
ELSE 0
END),
NotMeetsSLA = SUM(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 0
ELSE 1
END),
AverageTTR = AVG(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),
AVG(dFact.ActiveDuration) AverageTimeInActiveStatus,
AVG(dFact.PendingDuration) AverageTimeInPendingStatus
from dbo.IncidentDimvw _I
INNER JOIN
(
Select
DD.DateKey,
DailyAggregate = DD.CalendarDate,
WeeklyAggregate = DATEADD(DAY, (DD.WeekNumberInYear - 1)*7, CONVERT(nvarchar(4),DD.YearNumber) + '-1-1 00:00:00'),
MonthlyAggregate = DATEADD(MONTH, DD.MonthNumber - 1, CONVERT(nvarchar(4),DD.YearNumber) + '-1-1 00:00:00'),
QuarterlyAggregate = DATEADD(MONTH, (((DD.MonthNumber-1)/3) *3), CONVERT(nvarchar(4),DD.YearNumber) + '-1-1 00:00:00'),
YearlyAggregate = CONVERT(datetime, CONVERT(nvarchar(4),DD.YearNumber) + '-1-1 00:00:00')
from DateDim DD
) AggregatedDateDim
ON
AggregatedDateDim.DateKey =
CASE
WHEN @DateFilter = 'ClosedOn'
THEN CONVERT(nvarchar(8), _I.ClosedDate, 112)
WHEN @DateFilter = 'CreatedOn'
THEN CONVERT(nvarchar(8), _I.CreatedDate, 112)
WHEN @DateFilter = 'ResolvedOn'
THEN CONVERT(nvarchar(8), _I.ResolvedDate, 112)
ELSE NULL
END
Left outer join
(
select
ActiveDuration = SUM(
CASE
WHEN statusEnum.ID = 'IncidentStatusEnum.Active'
THEN dFact.TotalTimeMeasure
ELSE 0
END
),
PendingDuration = SUM(
CASE
WHEN statusEnum.ID = 'IncidentStatusEnum.Active.Pending'
THEN dFact.TotalTimeMeasure
ELSE 0
END
),
dFact.IncidentDimKey
from
dbo.IncidentStatusDurationFactvw dFact
Left outer join
dbo.IncidentStatusvw statusEnum
ON statusEnum.IncidentStatusId = dFact.IncidentStatusId
Group by dfact.IncidentDimKey
) dFact
ON dFact.IncidentDimKey = _I.IncidentDimKey
Where
((_I.Source_IncidentSourceId IN (select value from @tableSource)) OR (-1 IN (select value from @tableSource))) AND
((_I.Status_IncidentStatusId IN (select value from @tableStatus)) OR (-1 IN (select value from @tableStatus))) AND
((_I.Priority IN (select value from @tablePriority)) OR (-1 IN (select value from @tablePriority))) AND
((_I.Impact_IncidentImpactId IN (select value from @tableImpact)) OR (-1 IN (select value from @tableImpact))) AND
((_I.Urgency_IncidentUrgencyId IN (select value from @tableUrgency)) OR (-1 IN (select value from @tableUrgency))) AND
(@StartDate <= @EndDate) AND
(
(@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)))
)
group by
CASE
WHEN ( @AggregationType = 'Daily')
THEN AggregatedDateDim.DailyAggregate
WHEN ( @AggregationType = 'Weekly')
THEN AggregatedDateDim.WeeklyAggregate
WHEN ( @AggregationType = 'Monthly')
THEN AggregatedDateDim.MonthlyAggregate
WHEN ( @AggregationType = 'Quarterly')
THEN AggregatedDateDim.QuarterlyAggregate
WHEN ( @AggregationType = 'Yearly')
THEN AggregatedDateDim.YearlyAggregate
END
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_IncidentManagement_GetIncidentResolutionData TO reportuser
GO
Home