ServiceManager.Report.ConfigurationManagement.SP.GetServiceSummary.Install (Resource)

Element properties:

Source Code:

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

File Content: ServiceManager.Report.ConfigurationManagement.SP.GetServiceSummary.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_ConfigurationManagement_SP_GetServiceSummary' 


)


   DROP PROCEDURE dbo.ServiceManager_Report_ConfigurationManagement_SP_GetServiceSummary


GO





CREATE PROCEDURE dbo.ServiceManager_Report_ConfigurationManagement_SP_GetServiceSummary


	@DateFilter	nvarchar(256) = null,


	@StartDate	datetime,


	@EndDate	datetime,


	@Service	int,


	@IncidentPriority		nvarchar(max) = null, 


	@ProblemPriority	nvarchar(max) = null,


	@ChangePriority	nvarchar(max) = null


	


	


AS


BEGIN


  SET NOCOUNT ON





  DECLARE @Error int


  DECLARE @ExecError int


  


  DECLARE @tableIncidentPriority TABLE (value nvarchar(256))  


  INSERT @tableIncidentPriority (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@IncidentPriority)


  


  DECLARE @tableProblemPriority TABLE (value nvarchar(256))  


  INSERT @tableProblemPriority (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@ProblemPriority)


  


   DECLARE @tableChangePriority TABLE (value nvarchar(256))  


  INSERT @tableChangePriority (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@ChangePriority)


 


select distinct


          -----------------------------------------------------------------------------------


          S.ServiceDimKey,


          S.DisplayName AS Service,





          -----------------------------------------------------------------------------------


          I.TargetResolutionTime IncidentTargetResolutionTime,


          I.Status AS IncidentStatusValue,


          I.Status_IncidentStatusId As IncidentStatus_IncidentStatusId,


          I.Escalated AS IncidentsEscalated,


          I.CreatedDate AS IncidentCreatedDate,


          I.ResolvedDate AS IncidentResolvedDate,


          I.IncidentDimKey,





          MeetsSLA = 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,


          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	,


          TimeInMinutes = ISNULL(TotalBT.TimeWorked,0),


          -----------------------------------------------------------------------------------





          P.ProblemDimKey,


          P.CreatedDate AS ProblemCreatedDate,


          P.Description AS ProblemDescription,


          P.Priority AS ProblemPriority,





          P.KnownError AS ProblemKnowError,


          P.RequiresMajorProblemReview AS ProblemMajorReview,





          P.Workarounds,





          ProblemStatusEnum.ProblemStatusValue AS ProblemStatus,


          ProblemStatusEnum.ProblemStatusId AS ProblemStatusId,


          ProblemStatusEnum.ID AS ProblemStatusValue,


          -----------------------------------------------------------------------------------


          C.CreatedDate AS ChangeCreatedDate,


          C.ChangeRequestDimKey,


          C.Category AS ChangeCategory,





          ChangeStatusEnum.ChangeStatusValue AS ChangeStatus,


          ChangeStatusEnum.ID AS ChangeStatusValue


          


          -----------------------------------------------------------------------------------


          from





          dbo.ServiceDimvw S





          INNER JOIN dbo.ConfigItemDimvw CI


          ON CI.EntityDimKey = S.EntityDimKey





          INNER JOIN dbo.WorkItemAboutConfigItemFactvw WCIFact


          ON CI.ConfigItemDimKey = WCIFact.WorkItemAboutConfigItem_ConfigItemDimKey





          INNER JOIN dbo.WorkItemDimvw WI


          ON WCIFact.WorkItemDimKey = WI.WorkItemDimKey





          LEFT OUTER JOIN dbo.ProblemDimvw P


          ON P.EntityDimKey = WI.EntityDimKey





          LEFT OUTER JOIN dbo.ChangeRequestDimvw C


          ON C.EntityDimKey = WI.EntityDimKey





          LEFT OUTER JOIN dbo.IncidentDimvw I


          ON I.EntityDimKey = WI.EntityDimKey


         


          LEFT OUTER JOIN	ProblemStatusvw AS  ProblemStatusEnum


          ON  ProblemStatusEnum.ProblemStatusId = P.Status_ProblemStatusId





          -----------------------------------------------------------------------------------


          LEFT OUTER JOIN	dbo.IncidentStatusvw AS  IncidentStatusEnum


          ON  IncidentStatusEnum.IncidentStatusId = I.Status_IncidentStatusId





          LEFT OUTER JOIN


          (


          SELECT


          SUM(BT.TimeInMinutes) TimeWorked,


          WIBTFact.WorkItemDimKey


          from


          dbo.WorkItemHasBillableTimeFactvw WIBTFact





          LEFT OUTER JOIN


          dbo.BillableTimeDimvw BT


          ON BT.BillableTimeDimKey = WIBTFact.WorkItemHasBillableTime_BillableTimeDimKey





          GROUP BY WIBTFact.WorkItemDimKey


          ) TotalBT


          ON TotalBT.WorkItemDimKey = WI.WorkItemDimKey


          -----------------------------------------------------------------------------------





          LEFT OUTER JOIN


          dbo.ChangeStatusvw AS ChangeStatusEnum ON


          ChangeStatusEnum.ChangeStatusId = C.Status_ChangeStatusId








          WHERE





          (S.ServiceDimKey = @Service) AND


          ((-1 IN (@IncidentPriority)) OR (I.Priority IN (select value from @tableIncidentPriority))) AND


          ((-1 IN (@ProblemPriority)) OR (P.Priority IN (select value from @tableProblemPriority))) AND


          ((-1 IN (@ChangePriority)) OR (C.Priority IN (Select value from @tableChangePriority))) 


          


          SET @Error = @@ERROR





QuitError:


  


  RETURN @Error


END


GO





GRANT EXECUTE ON dbo.ServiceManager_Report_ConfigurationManagement_SP_GetServiceSummary TO reportuser


GO