ServiceManager.Report.Compliance.RiskManagement.UDF.ufnGetInherentRiskMapAxisData.Install (Resource)

Element properties:

Source Code:

<Resource ID="ServiceManager.Report.Compliance.RiskManagement.UDF.ufnGetInherentRiskMapAxisData.Install" Accessibility="Public" FileName="ServiceManager.Report.GRC.RiskManagement.UDF.ufnGetInherentRiskMapAxisData.Install.sql"/>

File Content: ServiceManager.Report.GRC.RiskManagement.UDF.ufnGetInherentRiskMapAxisData.Install.sql

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufnGetInherentRiskMapAxisData]') AND type in (N'TF'))


DROP FUNCTION [dbo].[ufnGetInherentRiskMapAxisData]


GO





SET ANSI_NULLS ON


GO





SET QUOTED_IDENTIFIER ON


GO








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


-- Author:		Milind Mahajan


-- Create date: 10/8/2009


-- Description:	<Description,,>


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


CREATE FUNCTION [dbo].[ufnGetInherentRiskMapAxisData]()


RETURNS @ScatterAxisData TABLE 


(


    -- Columns returned by the function


	xAxisMin tinyint


	, xAxisMid tinyint


	, xAxisMax tinyint


	, yAxisMin tinyint


	, yAxisMid tinyint


	, yAxisMax tinyint


)


AS 





BEGIN


	DECLARE @xAxisMin tinyint, @xAxisMax tinyint, @xAxisMid tinyint, @yAxisMin tinyint, @yAxisMax tinyint, @yAxisMid tinyint;


	


	SELECT @xAxisMin = MIN(CAST(RiskImpactValue AS tinyint)), @xAxisMax = MAX(CAST(RiskImpactValue AS tinyint)), 


@xAxisMid = (MIN(CAST (RiskImpactValue AS tinyint)) + ((MAX(CAST(RiskImpactValue AS tinyint)) - MIN(CAST(RiskImpactValue AS tinyint)))/2)) FROM RiskImpactvw WHERE ParentID IS NOT NULL





	SELECT @yAxisMin = MIN(CAST(RiskLikeLihoodValue AS tinyint)), @yAxisMax = MAX(CAST(RiskLikeLihoodValue AS tinyint)), @yAxisMid = (MIN(CAST(RiskLikeLihoodValue AS tinyint)) + ((MAX(CAST(RiskLikeLihoodValue AS tinyint)) - MIN(CAST(RiskLikeLihoodValue AS tinyint)))/2)) FROM RiskLikeLihoodvw WHERE ParentID IS NOT NULL





	INSERT @ScatterAxisData


	SELECT @xAxisMin, @xAxisMid, @xAxisMax, @yAxisMin, @yAxisMid, @yAxisMax


	


	RETURN;


END


GO








GRANT SELECT ON dbo.ufnGetInherentRiskMapAxisData TO reportuser


GO