PROC [dbo].[InvestmentPlans]
(@invest as bestScenario READONLY)
AS
DECLARE @R FLOAT  -- rate of interst
DECLARE @N INT  -- number of years 
DECLARE @P FLOAT  --Priniciple amount 
DECLARE @Cplan FLOAT  -- Current plan final amount 
declare @Nplan FLOAT  -- proposed plan  final amount 
DECLARE @Results  FLOAT  -- holds results of new plan
Declare @deptype char(6) -- deposit type
DECLARE @Custid char(4)  -- customer id 
DECLARE @Custname char(15) -- customer name 

-- Declare cursor 
Declare custCursor CURSOR 
local Scroll Keyset Optimistic

FOR SELECT Custid,CustName,Principal,NumberofYears,DepositType from @invest 

--open the cursor
OPEN custCursor

FETCH Next FROM custCursor INTO @custid,@custname,@P,@N,@deptype
While @@FETCH_STATUS =0 
BEGIN

	If @N>=20.0 set @R=15
	else if @N>=11.0 set @R=12.5
	else if @N>=6.0 set @R=10
	else if @N>=4.0 set @R=7.5
	else set @R=5 	

-- to check for saving customers 
	if  @deptype = 'Saving' 

-- calculate compound interest for saving customers::plan 1 
BEGIN 	
	 SET @Nplan= @P * (Power (@N,(1 + @R)))
	 SET @Cplan = @P + (@P * @N * @R) 
	 --SET @RESULTS =(((@Nplan-@Cplan)/@Cplan)*100)

--check to see if proposed plan @Nplan is > current @Cplan  
IF @Nplan > @Cplan *1.1
	BEGIN
	SELECT @custid AS CUST ,@deptype AS DESPOSITTYPE,'Plan 1 suitable' 
	END 
		ELSE
	BEGIN 
	SELECT @CUSTID AS CUST,@deptype AS DESPOSITTYPE,'Plan 1 is not suitable,the difference is,',@RESULTS,'%' 
	END
-- check for saving customers for plan 2	 
		SET @Nplan= @P + (@P * @N * (@R +2)) 
		SET @Cplan = @P + (@P * @N * @R)  
		--SET @RESULTS =((@Nplan-@Cplan)/@Cplan)*100	
 --compare proposed vs  current for > 10% rule  for  plan 2
		 	IF @Nplan > @Cplan *1.1
				
	BEGIN
	SELECT @custid AS CUST,@deptype AS DESPOSITTYPE,'PLAN 2 SUITABLE' 
	END 
			ELSE
	BEGIN 
	SELECT @custid AS CusT,@deptype AS DESPOSITTYPE,'NOT suitable,the difference is,',@RESULTS,'%' 
	END 
 END 

-- to check for Fixed customers 
if @deptype = 'Fixed'

-- Increase R by 2 %  for fixed for  plan 3 

BEGIN 
		SET @Nplan= @P * @N * (@R +2) 
		SET @Cplan = @P * (Power (@N,(1 + @R)))
		--SET @RESULTS =(((@Nplan-@Cplan)/@Cplan)*100)	

 -- compare proposed vs  plan for  plan 3 

		 	IF @Nplan > @Cplan *1.1
		BEGIN
		SELECT @custid AS CUST,@deptype AS DESPOSITTYPE,'Plan 3 SUITABLE' 
		END 
			ELSE
		BEGIN 
		SELECT @custid AS CusT,@deptype AS DESPOSITTYPE,'Plan 3 not suitable,the difference is,',@RESULTS,'%' 
		END 

-- Calculate fixed plans with 5% bonus for plan 4 

		SET @Cplan = @P * (Power (@N,(1 + @R)))
		set @Nplan= @P * (Power (@N,(1 + @R))) + (.05 *(( @P * (Power 			(@N,(1 + @R))))* @N))
		--SET @RESULTS =(((@Nplan-@Cplan)/@Cplan)*100)	 
	
-- compare proposed vs  current for >10%  for  plan 4
	 	
IF @Nplan > @Cplan *1.1
		BEGIN
		SELECT @custid AS CUST,@deptype AS DESPOSITTYPE,'Plan 4 SUITABLE' 
		END 
			ELSE
		BEGIN 
		SELECT @custid AS CusT,@deptype AS DESPOSITTYPE,'Plan 4 not suitable,the difference is,',@RESULTS,'%' 
		END 
	END 
	
 FETCH Next FROM custCursor INTO @custid,@custname,@P,@N,@deptype
 END 

CLOSE custCursor
DEALLOCATE custCursor


DECLARE  @cust as bestscenario


Insert into @cust values('1','aaa',100000,2,'Saving')
Insert into @cust values ('2','bbb',5000,5,'Fixed')
Insert into @cust values ('3','ccc',8200,4,'Saving')
Insert into @cust values ('4','ddd',15000,10,'Fixed')
Insert into @cust values ('5','eee',6500,20,'Fixed')



exec InvestmentPlans @cust