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]
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
``````
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x

SQL Server DBA & ArchitectCommented:
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

SQL Server DBA & ArchitectCommented:
Can you post some sample records and the desired output result set so that it would be helpful to understand your requirement better..
0

Author 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]
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
``````
sampledatatable.PNG
0

Author 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

SQL Server DBA & ArchitectCommented:
>> 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