ServiceManager.Report.ProblemManagement.SP.GetCIsWithMostIncidentsData.Install (Resource)

Element properties:

Source Code:

<Resource ID="ServiceManager.Report.ProblemManagement.SP.GetCIsWithMostIncidentsData.Install" Accessibility="Public" FileName="ServiceManager.Report.ProblemManagement.SP.GetCIsWithMostIncidentsData.Install.sql"/>

File Content: ServiceManager.Report.ProblemManagement.SP.GetCIsWithMostIncidentsData.Install.sql

SET ANSI_NULLS ON


GO


SET QUOTED_IDENTIFIER ON


GO








-- 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





CREATE PROCEDURE dbo.ServiceManager_Report_ProblemManagement_SP_GetCIsWithMostIncidentsData


@DateFilter		nvarchar(256) = null,


@StartDate		datetime,


@EndDate		datetime,


@Source 		nvarchar(max) = null,


@Urgency		nvarchar(max) = null,


@Impact			nvarchar(max) = null,


@Priority		nvarchar(max) = null,


@Classification nvarchar(max) = null,


@IncidentsPerCI int = 1


 


AS


BEGIN


  SET NOCOUNT ON





  DECLARE @Error int


  DECLARE @ExecError int


  


  SET @EndDate = DATEADD(DAY, 1, @EndDate)


  


  DECLARE @tableSource TABLE (value nvarchar(256))  


  INSERT @tableSource (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Source)


  


  DECLARE @tableUrgency TABLE (value nvarchar(256))  


  INSERT @tableUrgency (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Urgency)


  


  DECLARE @tablePriority TABLE (value nvarchar(256))  


  INSERT @tablePriority (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Priority)


  


  DECLARE @tableClassification TABLE (value nvarchar(256))  


  INSERT @tableClassification (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Classification)


  


  DECLARE @tableImpact TABLE (value nvarchar(256))  


  INSERT @tableImpact (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Impact)





  


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