SQL Query to count Functions
DECLARE @SQL VARCHAR(8000)
IF EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'FLOW_STEP_PROCESSING_INDICATOR' AND Object_ID = Object_ID(N'FUNCTION_AREA'))
BEGIN
SELECT @SQL = '
SELECT
COUNT(CASE WHEN (FLOW_STEP_PROCESSING_INDICATOR = 0) AND (FUNCTION_AREA_STEP_INDICATOR = 0) AND (OVERLAY_INDICATOR <> 1) AND (ACTIVE_INDICATOR = 1) THEN 1 ELSE NULL END) AS [Prompt Functions],
COUNT(CASE WHEN (FLOW_STEP_PROCESSING_INDICATOR = 0) AND (FUNCTION_AREA_STEP_INDICATOR = 1) AND (OVERLAY_INDICATOR <> 1) AND (ACTIVE_INDICATOR = 1) THEN 1 ELSE NULL END) AS [Prompt as Step Functions],
COUNT(CASE WHEN (FLOW_STEP_PROCESSING_INDICATOR = 1) AND (FUNCTION_AREA_STEP_INDICATOR = 1) AND (OVERLAY_INDICATOR <> 1) AND (ACTIVE_INDICATOR = 1) THEN 1 ELSE NULL END) AS [Step Functions],
COUNT(CASE WHEN (FLOW_STEP_PROCESSING_INDICATOR = 0) AND (FUNCTION_AREA_STEP_INDICATOR = 0) AND (OVERLAY_INDICATOR = 1) AND (ACTIVE_INDICATOR = 1) THEN 1 ELSE NULL END) AS [Overlays]
FROM FUNCTION_AREA'
EXEC sp_sqlexec @SQL
END
ELSE
BEGIN
IF EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'FUNCTION_AREA_STEP_INDICATOR' AND Object_ID = Object_ID(N'FUNCTION_AREA'))
BEGIN
SELECT @SQL = '
SELECT
COUNT(CASE WHEN (FUNCTION_AREA_STEP_INDICATOR = 0) AND (OVERLAY_INDICATOR <> 1) AND (ACTIVE_INDICATOR = 1) THEN 1 ELSE NULL END) AS [Prompt Functions], COUNT(CASE WHEN (FUNCTION_AREA_STEP_INDICATOR = 1) AND (OVERLAY_INDICATOR <> 1) AND (ACTIVE_INDICATOR = 1) THEN 1 ELSE NULL END) AS [Prompt as Step Functions], COUNT(CASE WHEN (FUNCTION_AREA_STEP_INDICATOR = 0) AND (OVERLAY_INDICATOR = 1) AND (ACTIVE_INDICATOR = 1) THEN 1 ELSE NULL END) AS [Overlays]
FROM FUNCTION_AREA'
EXEC sp_sqlexec @SQL
END
ELSE
BEGIN
SELECT @SQL = '
SELECT
COUNT(CASE WHEN (OVERLAY_INDICATOR <> 1) AND (ACTIVE_INDICATOR = 1) THEN 1 ELSE NULL END) AS [Prompt Functions],
COUNT(CASE WHEN (OVERLAY_INDICATOR = 1) AND (ACTIVE_INDICATOR = 1) THEN 1 ELSE NULL END) AS [Overlays]
FROM FUNCTION_AREA'
EXEC sp_sqlexec @SQL
END
END