ServiceManager.Report.Compliance.ControlManagement.SP.ControlManagementProgress.Install (Resource)

Element properties:

Source Code:

<Resource ID="ServiceManager.Report.Compliance.ControlManagement.SP.ControlManagementProgress.Install" Accessibility="Public" FileName="ServiceManager.Report.GRC.ControlManagement.SP.GetControlManagementProgress.Install.sql"/>

File Content: ServiceManager.Report.GRC.ControlManagement.SP.GetControlManagementProgress.Install.sql

/****** Object:  StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_ControlManagementProgressReport]    Script Date: 09/29/2009 06:00:09 ******/


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceManager_Report_GRC_ControlManagement_SP_ControlManagementProgressReport]') AND type in (N'P', N'PC'))


DROP PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_ControlManagementProgressReport]


GO





/****** Object:  StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_ControlManagementProgressReport]    Script Date: 09/29/2009 06:00:09 ******/


SET ANSI_NULLS ON


GO





SET QUOTED_IDENTIFIER ON


GO





-- =============================================


-- Author:		<Author,,Name>


-- Create date: <Create Date,,>


-- Last Modified By: MilindMa 2009-11-2


-- Description:	<Description,,>


-- =============================================


CREATE PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_ControlManagementProgressReport]


	@StartDate DATETIME,


	@EndDate DATETIME,


	@IntervalType NVARCHAR (20)


AS








DECLARE @ActualEndDate DateTime = DATEADD(day, 1, GETDATE());





DECLARE @COStatusDraft int, @COStatusPending int, @COStatusPublished int;





SELECT @COStatusDraft = COStatusID FROM COStatusvw WHERE COStatusValue = 'Draft';





SELECT @COStatusPending = COStatusID FROM COStatusvw WHERE COStatusValue = 'Pending';





SELECT @COStatusPublished = COStatusID FROM COStatusvw WHERE COStatusValue = 'Published';





IF DATEDIFF(day, @EndDate, @ActualEndDate) > 0


  BEGIN


    SELECT @ActualEndDate = @EndDate


  END





IF OBJECT_ID(N'tempdb..#ControlObjectTemp', N'U') IS NOT NULL


	BEGIN 


		DROP TABLE #ControlObjectTemp


	END





IF OBJECT_ID(N'tempdb..#TempObject', N'U') IS NOT NULL


	BEGIN 


		DROP TABLE #TempObject


	END


	


CREATE TABLE #ControlObjectTemp


	(


		Id INT IDENTITY(1,1),


		Interval varchar(20),


		StartDate DateTime,


		EndDate DateTime,


		DraftStatusCount Int,


		PendingStatusCount Int,


		PublishedStatusCount Int


	)


	


IF @IntervalType = 'Daily'


	BEGIN


		INSERT INTO #ControlObjectTemp (Interval, StartDate) 


			SELECT CONVERT(VARCHAR(10), CalendarDate, 101), CalendarDate


			FROM DateDim 


			WHERE CalendarDate >= @StartDate


			AND CalendarDate <= @ActualEndDate


			ORDER BY CalendarDate ASC


		


		-- Commeted by MilindMa on 11/2/09 since it is not needed.


		--DELETE FROM #ControlObjectTemp


			--WHERE StartDate > GETDATE()


		


		UPDATE Q 


			SET Q.DraftStatusCount = T.CountValue


			FROM #ControlObjectTemp Q JOIN 


			(SELECT C.Interval, Count(F.ControlObjectiveDimKey) as CountValue


			FROM ControlObjectiveStatusDurationFactvw F 	


			JOIN #ControlObjectTemp C 


			ON CONVERT(VARCHAR(10),C.StartDate,111)>= CONVERT(VARCHAR(10),F.LastModifiedDateTime,111) 


			AND CONVERT(VARCHAR(10),C.StartDate,111)< ISNUll(CONVERT(VARCHAR(10),F.FinishDateTime,111),GetDate())


			AND F.COStatusId = @COStatusDraft GROUP BY C.Interval) T ON T.Interval = Q.Interval


							


		UPDATE Q 


			SET Q.PendingStatusCount = T.CountValue


			FROM #ControlObjectTemp Q JOIN 


			(SELECT C.Interval, Count(F.ControlObjectiveDimKey) as CountValue


			FROM ControlObjectiveStatusDurationFactvw F 	


			JOIN #ControlObjectTemp C 


			ON CONVERT(VARCHAR(10),C.StartDate,111)>= CONVERT(VARCHAR(10),F.LastModifiedDateTime,111) 


			AND CONVERT(VARCHAR(10),C.StartDate,111)< ISNUll(CONVERT(VARCHAR(10),F.FinishDateTime,111),GetDate())


			AND F.COStatusId = @COStatusPending GROUP BY C.Interval) T ON T.Interval = Q.Interval





		UPDATE Q 


			SET Q.PublishedStatusCount = T.CountValue


			FROM #ControlObjectTemp Q JOIN 


			(SELECT C.Interval, Count(F.ControlObjectiveDimKey) as CountValue


			FROM ControlObjectiveStatusDurationFactvw F


			JOIN #ControlObjectTemp C 


			ON CONVERT(VARCHAR(10),C.StartDate,111)>= CONVERT(VARCHAR(10),F.LastModifiedDateTime,111) 


			AND CONVERT(VARCHAR(10),C.StartDate,111)<= ISNUll(CONVERT(VARCHAR(10),F.FinishDateTime,111),GetDate())


			AND F.COStatusId = @COStatusPublished GROUP BY C.Interval) T ON T.Interval = Q.Interval


	


		SELECT Interval, DraftStatusCount, PendingStatusCount, PublishedStatusCount FROM #ControlObjectTemp		


	END





IF @IntervalType = 'Monthly'


	BEGIN


		INSERT INTO #ControlObjectTemp (Interval, StartDate, EndDate) 


			SELECT CONVERT(Varchar(3),CalendarMonth) + ' ' + CONVERT(Varchar(4),YearNumber), MIN(CalendarDate), MAX(CalendarDate)


  			FROM DateDim


			WHERE CalendarDate >= @StartDate 


			AND CalendarDate <= @ActualEndDate


			GROUP BY Convert(Varchar(3),CalendarMonth) + ' ' + Convert(Varchar(4),YearNumber)


			ORDER BY MIN(CalendarDate)				


	END





ELSE IF @IntervalType = 'Weekly'


	BEGIN


		INSERT INTO #ControlObjectTemp (Interval, StartDate, EndDate) 


			SELECT 'W'+CONVERT(Varchar(3),WeekNumberInYear) + ' ' + CONVERT(Varchar(4),YearNumber), MIN(CalendarDate), MAX(CalendarDate)


  			FROM DateDim


			WHERE CalendarDate >= @StartDate 


			AND CalendarDate <= @ActualEndDate


			GROUP BY 'W'+Convert(Varchar(3),WeekNumberInYear) + ' ' + Convert(Varchar(4),YearNumber)


			ORDER BY MIN(CalendarDate)				


	END


	


ELSE IF @IntervalType = 'Quaterly'


	BEGIN


		INSERT INTO #ControlObjectTemp (Interval, StartDate, EndDate) 


			SELECT CONVERT(Varchar(3),CalendarQuarter) + ' ' + CONVERT(Varchar(4),YearNumber), MIN(CalendarDate), MAX(CalendarDate)


  			FROM DateDim


			WHERE CalendarDate >= @StartDate 


			AND CalendarDate <= @ActualEndDate


			GROUP BY Convert(Varchar(3),CalendarQuarter) + ' ' + Convert(Varchar(4),YearNumber)


			ORDER BY MIN(CalendarDate)				


	END





IF @IntervalType = 'Yearly'


	BEGIN


		INSERT INTO #ControlObjectTemp (Interval, StartDate, EndDate) 


			SELECT CONVERT(Varchar(4),YearNumber), MIN(CalendarDate), MAX(CalendarDate)


  			FROM DateDim


			WHERE CalendarDate >= @StartDate


			AND CalendarDate <= @ActualEndDate


			GROUP BY Convert(Varchar(4),YearNumber)


			ORDER BY MIN(CalendarDate)				


	END





IF @IntervalType = 'Monthly' OR @IntervalType = 'Quaterly' OR @IntervalType = 'Yearly' OR @IntervalType = 'Weekly'


	BEGIN


	


		CREATE TABLE #TempObject


			(


				ObjectID Int,


				StatusId Int,


				StartDate DateTime,


				EndDate DateTime


			)


		


		DECLARE @incr INT 


		DECLARE @StartdateTemp DateTime 


		DECLARE @EndDateTemp DateTime


		


		SET @incr = 1


		WHILE (@incr <= (SELECT count(1) FROM #ControlObjectTemp))


			BEGIN


			


			SELECT @StartdateTemp = StartDate, @EndDateTemp = EndDate FROM #ControlObjectTemp WHERE id = @incr


			


			INSERT INTO #TempObject (ObjectID, StatusId, StartDate, EndDate)


				SELECT ControlObjectiveDimKey, COStatusID, @StartdateTemp , @EndDateTemp 


				FROM ControlObjectiveStatusDurationFactvw F


				WHERE COStatusId = @COStatusPublished


				AND ((CAST(FLOOR(CAST(F.LastModifiedDateTime AS FLOAT)) AS DATETIME) Between @StartdateTemp AND @EndDateTemp) 


				OR ( CAST(FLOOR(CAST(ISNUll(F.FinishDateTime, GETDATE()) AS FLOAT)) AS DATETIME) Between @StartdateTemp	AND @EndDateTemp))


			


			INSERT INTO #TempObject (ObjectID, StatusId, StartDate, EndDate)


				SELECT ControlObjectiveDimKey, COStatusID, @StartdateTemp , @EndDateTemp 


				FROM ControlObjectiveStatusDurationFactvw F


				WHERE COStatusId = @COStatusPending


				AND ((CAST(FLOOR(CAST(F.LastModifiedDateTime AS FLOAT)) AS DATETIME) Between @StartdateTemp AND @EndDateTemp) 


				OR ( CAST(FLOOR(CAST(ISNUll(F.FinishDateTime, GETDATE()) AS FLOAT)) AS DATETIME) Between @StartdateTemp	AND @EndDateTemp))


				AND ControlObjectiveDimKey NOT IN (SELECT ObjectID FROM #TempObject)


				


			INSERT INTO #TempObject (ObjectID, StatusId, StartDate, EndDate)


				SELECT ControlObjectiveDimKey, COStatusID, @StartdateTemp , @EndDateTemp 


				FROM ControlObjectiveStatusDurationFactvw F


				WHERE COStatusId = @COStatusDraft


				AND ((CAST(FLOOR(CAST(F.LastModifiedDateTime AS FLOAT)) AS DATETIME) Between @StartdateTemp AND @EndDateTemp) 


				OR ( CAST(FLOOR(CAST(ISNUll(F.FinishDateTime, GETDATE()) AS FLOAT)) AS DATETIME) Between @StartdateTemp	AND @EndDateTemp))


				AND ControlObjectiveDimKey NOT IN (SELECT ObjectID FROM #TempObject)


				


			UPDATE Q 


				SET Q.DraftStatusCount = T.CountValue


				FROM #ControlObjectTemp Q JOIN 


				(SELECT COUNT(F.StatusId) as CountValue, C.StartDate, C.EndDate


				FROM #TempObject F 	


				JOIN #ControlObjectTemp C 


				ON F.StartDate = C.StartDate AND F.EndDate = C.EndDate


				AND F.StatusId = @COStatusDraft GROUP BY F.StatusId, C.StartDate, C.EndDate) T 


				ON T.StartDate = Q.StartDate AND T.EndDate = Q.EndDate


				


			UPDATE Q 


				SET Q.PendingStatusCount = T.CountValue


				FROM #ControlObjectTemp Q JOIN 


				(SELECT COUNT(F.StatusId) as CountValue, C.StartDate, C.EndDate


				FROM #TempObject F 	


				JOIN #ControlObjectTemp C 


				ON F.StartDate = C.StartDate AND F.EndDate = C.EndDate


				AND F.StatusId = @COStatusPending GROUP BY F.StatusId, C.StartDate, C.EndDate) T 


				ON T.StartDate = Q.StartDate AND T.EndDate = Q.EndDate


		


		


			UPDATE Q 


				SET Q.PublishedStatusCount = T.CountValue


				FROM #ControlObjectTemp Q JOIN 


				(SELECT COUNT(F.StatusId) as CountValue, C.StartDate, C.EndDate


				FROM #TempObject F 	


				JOIN #ControlObjectTemp C 


				ON F.StartDate = C.StartDate AND F.EndDate = C.EndDate


				AND F.StatusId = @COStatusPublished GROUP BY F.StatusId, C.StartDate, C.EndDate) T 


				ON T.StartDate = Q.StartDate AND T.EndDate = Q.EndDate


			


			DELETE FROM #TempObject


			


			SET @incr = @incr + 1


		END


		


		SELECT Interval, DraftStatusCount, PendingStatusCount, PublishedStatusCount FROM #ControlObjectTemp


END





GO





GRANT EXECUTE ON dbo.ServiceManager_Report_GRC_ControlManagement_SP_ControlManagementProgressReport TO reportuser


GO