how to read and manipulate more than one line of data in a tvp

I have a sp that i am trying to get to read in more than one line of data and check a constraint
The math is working only on the last tuple -
how would i get this to work for each tuple with the deposit type of 'saving'

PROC [dbo].[InvestmentPlans]
(@invest as bestScenario READONLY )
AS
DECLARE @CustId Char(4) -- customer ID
DECLARE @R FLOAT  -- rate of interst
DECLARE @N INT  -- number of years 
DECLARE @P FLOAT  --Prinicipal amount
DECLARE @NP Float --New Principal amount
DECLARE @F Float -- Final Amount 
DECLARE @Cplan FLOAT  -- Current plan final amount 
DECLARE @Nplan FLOAT  -- proposed plan  final amount 
Declare @deptype char(6) -- deposit type
DECLARE @D Float --difference between current plan and new plan

SELECT @CustId = CustID, @deptype = DepositType,@N = NumberOfYears,@R = Rate
FROM @invest

-- calculate compound interest for saving customers::plan 1 

Begin
	SELECT @P = PrincipalAmount FROM @invest
	SET @NP =  @P * (Power (@N,(1 + @R)))
	SET @Nplan= SUM(@NP)
	SELECT @Cplan = SUM(FinalAmount)FROM @invest
	SET @D = (((@Nplan - @Cplan)/@Cplan)*100)
	PRINT @CustID
	PRINT @p
	PRINT @NP
	PRINT @Nplan
	PRINT @Cplan
	PRINT @deptype
	PRINT @N
	PRINT @R

--check to see if proposed plan @Nplan is > current @Cplan  
IF @D < 10
BEGIN
   	PRINT 'Plan 1 suitable' 
	PRINT @D
END
ELSE IF @D > 10
BEGIN
		PRINT 'Plan 1 is not suitable,the difference is'
		PRINT @D
		PRINT '%' 

END
		
SELECT *
FROM @invest

END

Open in new window

jrb47Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Can you post some sample records and the desired output result set so that it would be helpful to understand your requirement better..
0
jrb47Author Commented:
 Okay, I have all of the data passing correctly - i think!
the image is the sample data i am working with

the SP is to compare two investment scenarios based on this
 
Plan-1: Giving compound interests to all savings account holders.  
Plan-2:  Increase all savings account interest rates by 2%.  
Plan-3:  Increase all fixed account interest rates by 2%.  
Plan-4: Giving 5% bonus per year for all fixed account holders.  

Here is the code - at this point I am more concerned about my math (again I think) as I have been looking at this so long my eyes are crossing!



USE [Unit11]
GO

/****** Object:  StoredProcedure [dbo].[InvestmentPlans]    Script Date: 04/01/2010 18:58:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[InvestmentPlans]
(@invest as bestScenario READONLY )
AS
DECLARE @CustId Char(4) -- customer ID
DECLARE @R FLOAT  -- rate of interst
DECLARE @N INT  -- number of years 
DECLARE @P FLOAT  --Prinicipal amount
DECLARE @NP Float --New Principal amount
DECLARE @F Float -- Final Amount 
DECLARE @Cplan FLOAT  -- Current plan final amount 
DECLARE @Nplan1 FLOAT  -- proposed plan1  final amount
DECLARE @Nplan2 FLOAT -- proposed plan2 final amount
DECLARE @Nplan3 FLOAT -- proposed plan3 final amount
DECLARE @Nplan4 FLOAT -- proposed plan4 final amount
Declare @deptype char(6) -- deposit type
DECLARE @D1 Float --difference between current plan1 and new plan1
DECLARE @D2 Float --difference between current plan2 and new plan2
DECLARE @D3 Float --difference between current plan3 and new plan3
DECLARE @D4 Float --difference between current plan4 and new plan4

-- Declare cursor 
Declare custCursor CURSOR 
local Scroll Keyset Optimistic


FOR SELECT CustID, DepositType,NumberOfYears,Rate,PrincipalAmount
FROM @invest
SET @Nplan1 = 0
SET @Nplan2 = 0
SET @Nplan3 = 0
SET @Nplan4 = 0
SELECT @Cplan = SUM(FinalAmount)FROM @invest

OPEN custCursor

FETCH Next FROM custCursor INTO @custid,@deptype, @N, @R,@P

While @@FETCH_STATUS =0 

BEGIN

-- calculate compound interest for saving customers::plan 1 
BEGIN
	--SELECT @Cplan = SUM(FinalAmount)FROM @invest
	IF @deptype = 'saving'
	SET @NP =  @P * (Power (@N,(1 + @R)))
	ELSE IF @deptype = 'fixed'
	SET @NP = @P
	SET @Nplan1= @Nplan1 +@NP
	SET @D1 = ((@Nplan1-@Cplan)/@Cplan)*100
END

-- check for saving customers for plan 2
BEGIN
	IF @deptype = 'saving'
	SET @NP = @P + (@P * @N * (@R +.02))
	ELSE IF @deptype = 'fixed'
	SET @NP = @P 
	SET @Nplan2= @Nplan2 +@NP
	SET @D2 = (((@Nplan2 - @Cplan)/@Cplan)*100)
END	

-- Increase R by 2 %  for fixed for  plan 3 
BEGIN
	IF @deptype = 'fixed'
	SET @NP = @P * @N * (@R +2)
	ELSE IF @deptype = 'saving'
	SET @NP = @NP
	SET @Nplan3= @Nplan3 +@P
	SET @D3 = (((@Nplan3 - @Cplan)/@Cplan)*100)
END   	

-- Calculate fixed plans with 5% bonus for plan 4
BEGIN
	IF @deptype = 'fixed'
	SET @NP= @P * (Power (@N,(1 + @R))) + (.05 *(( @P * (Power(@N,(1 + @R))))* @N))
	ELSE IF @deptype = 'saving'
	SET @NP = @P
	SET @Nplan4 = @Nplan4 +@NP
	SET @D4 = (((@Nplan4 - @Cplan)/@Cplan)*100)
END 

FETCH Next FROM custCursor INTO @custid,@deptype, @N, @R,@P	
END

CLOSE custCursor
DEALLOCATE custCursor


BEGIN
PRINT @Cplan
PRINT @Nplan1
PRINT @D1

IF @D1 < 10
BEGIN
	PRINT 'Plan 1 is suitable' 
END

ELSE IF @D1 > 10
BEGIN
	PRINT 'Plan 1 is not suitable,the difference is'
	PRINT @D1
	PRINT '%'
END
END

 --compare proposed vs  current for > 10% rule  for  plan 2
BEGIN
	PRINT '_________________________________________________________'
	PRINT @Cplan
	PRINT @Nplan2
	PRINT @D2
	
IF @D2 < 10
BEGIN
   	PRINT 'Plan 2 suitable' 
END 
ELSE IF @D2 > 10 
BEGIN
	PRINT 'Plan 2 is not suitable,the difference is'
	PRINT @D2
	PRINT '%' 
END
END	

 -- compare proposed vs  plan for  plan 3 
BEGIN
	PRINT '_________________________________________________________'
	PRINT @Cplan
	PRINT @Nplan3
	PRINT @D3
	
IF @D3 < 10
BEGIN
   	PRINT 'Plan 3 suitable' 
END
ELSE IF @D3 > 10
BEGIN
		PRINT 'Plan 3 is not suitable,the difference is'
		PRINT @D3
		PRINT '%' 
END	

-- compare proposed vs  current for >10%  for  plan 4
BEGIN
	PRINT '_________________________________________________________'
	PRINT @Cplan
	PRINT @Nplan4
	PRINT @D4
	
IF @D4 < 10
BEGIN
   	PRINT 'Plan 4 suitable' 
END
ELSE IF @D4 > 10
BEGIN
		PRINT 'Plan 4 is not suitable,the difference is'
		PRINT @D4
		PRINT '%' 
 END
 END

SELECT *
FROM @invest
END

GO

Open in new window

sampledatatable.PNG
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
One thought..
have you tried inserting all the required set of records into a temp table and do all sorts of manipulations on the records at a single shot instead of doing it record by record wise as you are doing it now..
If you have tried earlier and that logic will not work, let me know so that we can try tuning in your current code since the earlier approach would be more efficient if you have more no. of records in your table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jrb47Author Commented:
The data is read into a TVP, I am not sure the code to accomplish this across a column and the rates will vary for each person......The program works as it is.....I am just sure there has to be another way. I was told this could be done without using cursors but can not figure it out.

0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> I was told this could be done without using cursors but can not figure it out.

Regret for the delay as it is a weekend..
And hope you sorted it out..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.