-- 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_ProblemManagement_SP_GetCIsWithMostIncidentsData'
)
DROP PROCEDURE dbo.ServiceManager_Report_ProblemManagement_SP_GetCIsWithMostIncidentsData
GO
Select SelectedCI.ConfigItemDimKey,
CIDvw.DisplayName,
SelectedCI.IncidentCount,
P.ProblemDimKey,
CR.ChangeRequestDimKey
from
(
select
CI.ConfigItemDimKey,
COUNT(I.IncidentDimKey) IncidentCount
from
IncidentDimvw I
INNER JOIN
WorkItemDimvw WI
ON I.EntityDimKey = WI.EntityDimKey
INNER JOIN
WorkItemAboutConfigItemFactvw relFct
ON WI.WorkItemDimKey = relFct.WorkItemDimKey AND relFct.DeletedDate IS NULL
INNER JOIN
ConfigItemDimvw CI
ON relFct.WorkItemAboutConfigItem_ConfigItemDimKey = CI.ConfigItemDimKey
LEFT OUTER JOIN
IncidentSourcevw AS SourceEnum
ON SourceEnum.IncidentSourceId = I.Source_IncidentSourceId
LEFT OUTER JOIN
IncidentClassificationvw AS ClassificationEnum
ON ClassificationEnum.IncidentClassificationId = I.Classification_IncidentClassificationId
LEFT OUTER JOIN
IncidentStatusvw AS StatusEnum
ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId
LEFT OUTER JOIN
IncidentUrgencyvw AS UrgencyEnum
ON UrgencyEnum.IncidentUrgencyId = I.Urgency_IncidentUrgencyId
LEFT OUTER JOIN
dbo.IncidentImpactvw AS ImpactEnum
ON ImpactEnum.IncidentImpactId = I.Impact_IncidentImpactId
where I.Source IS NOT NULL AND
I.IsDeleted = 0 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)))
)
AND
(@StartDate <= @EndDate) AND
((-1 IN (Select value from @tableSource)) OR (SourceEnum.IncidentSourceId IN (Select value from @tableSource))) AND
((-1 IN (Select value from @tableUrgency)) OR (UrgencyEnum.IncidentUrgencyId IN (Select value from @tableUrgency))) AND
((-1 IN (Select value from @tableImpact)) OR (ImpactEnum.IncidentImpactId IN (Select value from @tableImpact))) AND
((-1 IN (Select value from @tableClassification)) OR (ClassificationEnum.IncidentClassificationId IN (Select value from @tableClassification))) AND
((-1 IN (Select value from @tablePriority)) OR (I.Priority IN (Select value from @tablePriority)))
GROUP BY CI.ConfigItemDimKey
HAVING COUNT(I.IncidentDimKey) >= @IncidentsPerCI
)SelectedCI
left join WorkItemAboutConfigItemFactvw relFct
ON SelectedCI.ConfigItemDimKey = relFct.WorkItemAboutConfigItem_ConfigItemDimKey and
relFct.DeletedDate IS NULL
left join WorkItemDimvw WI
ON WI.WorkItemDimKey = relFct.WorkItemDimKey
left join ChangeRequestDimvw CR
ON CR.EntityDimKey = WI.EntityDimKey
left join ProblemDimvw P
ON P.EntityDimKey = WI.EntityDimKey
left join ConfigItemDimvw CIDvw
ON CIDvw.ConfigItemDimKey = SelectedCI.ConfigItemDimKey
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_ProblemManagement_SP_GetCIsWithMostIncidentsData TO reportuser
GO