-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION FindCurrentValue 
(
	-- Add the parameters for the function here
	@Postcode nvarchar(50),@Application_Date date,@HouseType nvarchar(50), @decPrice money
)
RETURNS int
AS
BEGIN
	-- Declare the return variable here
	DECLARE @currentValue money
	DECLARE @CurrentYear int
	DECLARE @CurrentMonth int
	DECLARE @NoOfLoops int
	DECLARE @CurrentLoop int
	DECLARE @CurrentAverage decimal
	
	SET @CurrentYear = YEAR(@Application_Date)
	SET @currentValue = @decPrice
	set @CurrentMonth = MONTH(@Application_DATE)
	
	WHILE(@CurrentYear <=YEAR(GETDATE()))
	BEGIN
	SET @CurrentLoop =0
	
	
	IF(YEAR(GETDATE()) = @CurrentYear)
	BEGIN
	SET @NoOfLoops = MONTH(GETDATE())-@CurrentMonth
	END
	
	IF(@CurrentYear = YEAR(@Application_Date))
	BEGIN
	SET @NoOfLoops = 12 - MONTH(@Application_Date)
	END
	
	IF(@CurrentYear >YEAR(@Application_Date) AND @CurrentYear < YEAR(GETDATE()))
	BEGIN
	SET @NoOfLoops = 12
	END
	
		SELECT @CurrentAverage =  (SELECT Top 1 Avg_Monthly_Increase FROM YearlyAverageIncrease WHERE @Postcode = Postcode AND @HouseType = HouseType AND YEAR = @CurrentYear)

	
	WHILE(@CurrentLoop < @NoOfLoops)
	Begin
	
	-- Add the T-SQL statements to compute the return value here
	SET @currentValue = @CurrentValue + ((@currentValue/100) * @CurrentAverage)

	SET @CurrentLoop = @CurrentLoop +1
	END
	
	Set @CurrentYear = @CurrentYear +1
	SET @CurrentLoop = @CurrentLoop +1
	END
	
	
	-- Return the result of the function
	RETURN @currentValue

END

CREATE FUNCTION dbo.fnFindCurrentValue
(
	@PostCode NVARCHAR(50),
	@ApplicationDate DATE,
	@HouseType NVARCHAR(50),
	@Price MONEY
)
RETURNS INT
AS
BEGIN
	RETURN	(
			SELECT		@Price * EXP(SUM(LOG(1 + x.CurrentAverage / 100E)))
			FROM		(
						SELECT	 2 UNION ALL
						SELECT	 3 UNION ALL
						SELECT	 4 UNION ALL
						SELECT	 5 UNION ALL
						SELECT	 6 UNION ALL
						SELECT	 7 UNION ALL
						SELECT	 8 UNION ALL
						SELECT	 9 UNION ALL
						SELECT	10 UNION ALL
						SELECT	11
					) AS m(Iterations)
			CROSS JOIN	(
						SELECT	TOP(1)
							Avg_Monthly_Increase
						FROM	dbo.YearlyAverageIncrease
						WHERE	PostCode = @PostCode
							AND HouseType = @HouseType
							AND [Year] = DATEPART(YEAR, @ApplicationDate)
							AND Avg_Monthly_Increase > -100
					) AS x(CurrentAverage)
			WHERE		m.Iterations >= DATEPART(MONTH, @Application_Date)
		)
END