SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
ALTER FUNCTION FindCurrentValue 
(
	-- Add the parameters for the function here
	@Postcode nvarchar(50),@Application_Date date,@HouseType nvarchar(50), @decPrice money
)
RETURNS money

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
GO

quote:

---

SET @currentValue = @CurrentValue + ((@currentValue/100) * @CurrentAverage)

---