ServiceManager.Report.ChangeManagement.SP.GetChangeManagementKPITrendData.Install (Resource)

Element properties:

Source Code:

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

File Content: ServiceManager.Report.ChangeManagement.SP.GetChangeManagementKPITrendData.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_ChangeManagement_SP_GetChangeManagementKPITrendData_Install' 


)


   DROP PROCEDURE dbo.ServiceManager_Report_ChangeManagement_SP_GetChangeManagementKPITrendData_Install


GO





CREATE PROCEDURE dbo.ServiceManager_Report_ChangeManagement_SP_GetChangeManagementKPITrendData_Install


	@AssignedTo	int = 0, 


	@CreatedBy	int = 0, 


	@DateFilter	nvarchar(256) = 'Created',


	@StartDate	datetime,


	@EndDate	datetime,   


	@ID			nvarchar(max)  = null,


	@Description	nvarchar(max) = null,


	@Status		nvarchar(max) = -1, 


	@Priority	nvarchar(max) = -1,


	@Impact		nvarchar(max) = -1,


	@Risk		nvarchar(max) = -1,


	@Category	nvarchar(max) = -1,


	@ChangeResult	nvarchar(max) = -1,


	@ActivityName	nvarchar(max) = null,


	@ActivityID		nvarchar(max) = null,	


	@ActivityStatus	nvarchar(max) = -1,


	@ActivityStage	nvarchar(max) = -1,	


	@ActivityType   nvarchar(max) =null,


	@CIName		nvarchar(max) = 0,


	@ScheduledStartFrom datetime = null,


	@ScheduledStartTo	datetime = null,


	@ScheduledEndFrom	datetime = null,


	@ScheduledEndTo		datetime = null,


	@ActualStartFrom	datetime = null,


	@ActualStartTo		datetime = null,


	@ActualEndFrom		datetime = null,


	@ActualEndTo		datetime = null,


	@LanguageCode nvarchar(max)= 'ENU'


	


	


AS


BEGIN


  SET NOCOUNT ON





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


  SET @ScheduledStartTo = DateAdd(DAY, 1, @ScheduledStartTo)


  SET @ScheduledEndTo = DateAdd(DAY, 1, @ScheduledEndTo)


  SET @ActualEndTo = DateAdd(DAY, 1, @ActualEndTo)


  SET @ActualStartTo = DateAdd(DAY, 1, @ActualStartTo)


  


  DECLARE @Error int


  DECLARE @ExecError int


  


  DECLARE @tableID TABLE (value nvarchar(256))  


  INSERT @tableID (value)


  SELECT * FROM dbo.fn_CSVToTableString(ISNULL(@ID,''))


  


  DECLARE @tableActivityID TABLE (value nvarchar(256))  


  INSERT @tableActivityID (value)


  SELECT * FROM dbo.fn_CSVToTableString(ISNULL(@ActivityID,''))


  


  DECLARE @tableStatus TABLE (value nvarchar(256))  


  INSERT @tableStatus (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Status)


  


  DECLARE @tablePriority TABLE (value nvarchar(256))  


  INSERT @tablePriority (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Priority)


  


  DECLARE @tableImpact TABLE (value nvarchar(256))  


  INSERT @tableImpact (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Impact)





  DECLARE @tableRisk TABLE (value nvarchar(256))  


  INSERT @tableRisk (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Risk)


  


  DECLARE @tableCategory TABLE (value nvarchar(256))  


  INSERT @tableCategory (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Category)


  


  DECLARE @tableChangeResult TABLE (value nvarchar(256))  


  INSERT @tableChangeResult (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@ChangeResult)


  


  DECLARE @tableActivityStatus TABLE (value nvarchar(256))  


  INSERT @tableActivityStatus (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@ActivityStatus)


  


  DECLARE @tableActivityStage TABLE (value nvarchar(256))  


  INSERT @tableActivityStage (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@ActivityStage)  


       


    SELECT DISTINCT 


	C.ActualStartDate,


	C.ActualEndDate, 	


	C.Category,


	C.CreatedDate,		


	ISNULL(StatusDS.DisplayName, StatusEnum.ChangeStatusValue) as Status,	


    StatusEnum.ID AS StatusValue,    


    Count(ContainsActivity.ActivityDimKey) as NumberOfActivities,


    Sum(CASE WHEN ContainsActivity.Status_ActivityStatusId = 6 THEN 1 ELSE 0 END) as NumberOfFailedActivities,


    Sum(CASE WHEN ContainsActivity.Status_ActivityStatusId = 1 THEN 1 ELSE 0 END) as NumberOfCompletedActivities    


    FROM 


	dbo.ChangeRequestDimvw C 	


		


	INNER JOIN 


	(


		


		Select 


			DD.DateKey,


			DD.CalendarDate,


			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 = CONVERT(nvarchar(8), C.CreatedDate, 112)


		INNER JOIN 	dbo.WorkItemDimvw WI ON (WI.EntityDimKey = C.EntityDimKey)	


		left JOIN dbo.WorkItemAssignedToUserFactvw WIATU 


						ON	(WI.WorkItemDimKey = WIATU.WorkItemDimKey)


						and WIATU.DeletedDate IS NULL		


						and WIATU.DateKey >= CONVERT(nvarchar(8), WI.CreatedDate, 112)				


		left JOIN	dbo.UserDimvw AS AssignedToUserDim 


						ON WIATU.WorkItemAssignedToUser_UserDimKey = AssignedToUserDim.UserDimKey


		left JOIN dbo.WorkItemCreatedByUserFactvw WICBU 


						ON (WI.WorkItemDimKey = WICBU.WorkItemDimKey)


						AND WICBU.DeletedDate IS NULL


						AND WICBU.DateKey >= CONVERT(nvarchar(8), WI.CreatedDate, 112)	


		left JOIN	dbo.UserDimvw AS CreatedByUserDim 


						ON WICBU.WorkItemCreatedByUser_UserDimKey = CreatedByUserDim.UserDimKey 	


		left outer join dbo.ChangeStatusvw AS StatusEnum 


						ON StatusEnum.ChangeStatusId = C.Status_ChangeStatusId 		


		left JOIN 	dbo.WorkItemContainsActivityFactvw AS WICAF 


						ON WI.WorkItemDimKey = WICAF.WorkItemDimKey


						AND WICAF.DeletedDate IS NULL		


						AND WICAF.DateKey >= CONVERT(nvarchar(8), WI.CreatedDate, 112)		


		left JOIN 	dbo.ActivityDimvw AS ContainsActivity 


						ON WICAF.WorkItemContainsActivity_ActivityDimKey = ContainsActivity.ActivityDimKey	


		left JOIN 	dbo.EntityManagedTypeFactvw AS EMTF 


					ON ContainsActivity.EntityDimKey = EMTF.EntityDimKey


					AND EMTF.DeletedDate IS NULL								


		left JOIN 	dbo.ManagedTypeDim AS ActivityType 


					ON EMTF.ManagedTypeDimKey = ActivityType.ManagedTypeDimKey	


		left JOIN 	dbo.DisplayStringDimvw StatusDS


					ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId


					AND StatusDS.LanguageCode = @LanguageCode					


	 		


    WHERE


	(@StartDate <= @EndDate) AND


	(


		(@ID IS NULL) OR (C.Id IN (Select value from @tableID))


	) AND	


	((C.CreatedDate >= @StartDate) AND (C.CreatedDate < @EndDate)) AND


	((@ScheduledStartFrom IS NULL) OR (C.ScheduledStartDate >= @ScheduledStartFrom)) AND


	((@ScheduledStartTo IS NULL) OR (C.ScheduledStartDate < @ScheduledStartTo)) AND





	((@ScheduledEndFrom IS NULL) OR (C.ScheduledEndDate >= @ScheduledEndFrom)) AND


	((@ScheduledEndTo IS NULL) OR (C.ScheduledEndDate < @ScheduledEndTo)) AND





	((@ActualStartFrom IS NULL) OR (C.ActualStartDate >= @ActualStartFrom)) AND


	((@ActualStartTo IS NULL) OR (C.ActualStartDate < @ActualStartTo))AND





	((@ActualEndFrom IS NULL) OR (C.ActualEndDate >= @ActualEndFrom)) AND


	((@ActualEndTo IS NULL) OR (C.ActualEndDate < @ActualEndTo)) AND





	((@AssignedTo = 0) OR @AssignedTo = AssignedToUserDim.UserDimKey) AND


	((@CreatedBy = 0) OR @CreatedBy = CreatedByUserDim.UserDimKey) AND


	


	((@ActivityID IS NULL) OR (ContainsActivity.Id IN (Select value from @tableActivityID))) AND


	


	((@ActivityType IS NULL) OR (@ActivityType = ActivityType.TypeName)) AND


	((@ActivityName IS NULL) OR (ContainsActivity.Title LIKE @ActivityName)) AND		


		


	((-1 IN (Select value from @tableStatus)) OR (C.Status_ChangeStatusId IN (Select value from @tableStatus))) AND


	((-1 IN (Select value from @tablePriority)) OR (C.Priority_ChangePriorityId  IN (Select value from @tablePriority))) AND


	((-1 IN (Select value from @tableImpact)) OR (C.Impact_ChangeImpactId  IN (Select value from @tableImpact))) AND


	((-1 IN (Select value from @tableRisk)) OR (C.Risk_ChangeRiskId  IN (Select value from @tableRisk))) AND


	((-1 IN (Select value from @tableCategory)) OR (C.Category_ChangeCategoryId  IN (Select value from @tableCategory))) AND


	((-1 IN (Select value from @tableActivityStatus)) OR (ContainsActivity.Status_ActivityStatusId IN (Select value from @tableActivityStatus))) AND


	((-1 IN (Select value from @tableActivityStage)) OR (ContainsActivity.Stage_ActivityStageId IN (Select value from @tableActivityStage))) AND


	((-1 IN (Select value from @tableChangeResult)) OR (C.ImplementationResults_ChangeImplementationResultsId IN (Select value from @tableChangeResult)))  AND			


	


	


	((@CIName = 0)


	OR


	(WI.WorkItemDimKey IN 


		(


			SELECT DISTINCT


				AICFact.WorkItemDimKey 


			FROM 


				dbo.WorkItemAboutConfigItemFactvw AICFact


				


				INNER JOIN dbo.ConfigItemDimvw CI


				ON AICFact.WorkItemAboutConfigItem_ConfigItemDimKey = CI.ConfigItemDimKey								


				WHERE 


				((@CIName =0) OR (CI.ConfigItemDimKey = @CIName))


		)


	)) 


Group By 


	C.ChangeRequestDimKey,     


	WI.WorkItemDimKey, 


	C.CreatedDate, 


	C.ScheduledStartDate,


	C.ScheduledEndDate, 


	C.ActualStartDate, 


	C.ActualEndDate,


	C.Category,


	ISNULL(StatusDS.DisplayName, StatusEnum.ChangeStatusValue),


	StatusEnum.ID








  SET @Error = @@ERROR





QuitError:


  


  RETURN @Error


END	


GO





GRANT EXECUTE ON dbo.ServiceManager_Report_ChangeManagement_SP_GetChangeManagementKPITrendData_Install TO reportuser


GO