MINOVA DEMO

ALTER PROCEDURE [dbo].[PRPTAIDATAHEADER] 

(

	@SelectionID VARCHAR(20)

	,@Year VARCHAR(20)

	,@Month VARCHAR(20)

)

AS

--DECLARE @SelectionID VARCHAR(20) = '0005'

--DECLARE @Year VARCHAR(20) = ''

--DECLARE @Month VARCHAR(20) = '01'

IF(@Month <> '' AND @Year <> '')

BEGIN

	SELECT dbo.fn_formatdatetime((@Year + @Month + '01'),'mmmm yyyy') AS PeriodStart, dbo.fn_formatdatetime((@Year + @Month + '01'),'mmmm yyyy') AS PeriodEnd 

			,* FROM AI_SELECTIONS

	WHERE Selection_ID = @SelectionID

END

ELSE

BEGIN

	DECLARE @TblRefAI VARCHAR(200)

	DECLARE @TblRefAIDesc VARCHAR(200)

	SELECT @TblRefAI = TableRefAI, @TblRefAIDesc = Selection_Description FROM AI_SELECTIONS

	WHERE Selection_ID = @SelectionID

	DECLARE @TblPeriod TABLE

	(	

		Period VARCHAR(50)

	)

	DECLARE @StrAI VARCHAR(MAX)

	SET @StrAI = 'SELECT DISTINCT Year+Month AS Period ' + ' FROM ' + @TblRefAI 

					+ ' WHERE (Year = ''' + @Year + ''' OR ''' + @Year + ''' = '''')'

					+ ' AND (Month = ''' + @Month + ''' OR ''' + @Month + ''' = '''')'

	--SELECT @StrAI

	INSERT INTO @TblPeriod

	EXEC(@StrAI)	

	SELECT dbo.fn_formatdatetime((MIN(Period)+'01'), 'mmmm yyyy') AS PeriodStart, dbo.fn_formatdatetime((MAX(Period)+'01'), 'mmmm yyyy') AS PeriodEnd

			,@TblRefAIDesc AS Selection_Description

	FROM @TblPeriod

END