SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Update with subquery values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Gregorys05
Starting Member

5 Posts

Posted - 06/20/2013 :  11:12:58  Show Profile  Reply with Quote
Hi all,
I have the below query


DECLARE @LoanValue decimal(38,20)
DECLARE @APR decimal(20,15)
DECLARE @FullTerm int
DECLARE @CurrentPeriod int
DECLARE @ALAGU decimal(20,15)

SET @LoanValue     = -20560
SET @APR           = 0.11  --11%
SET @FullTerm      = 128  --300 months = 25 years
SET @CurrentPeriod = 1+1 --For which payment period do you want the payment breakdown?
Set @ALAGU         = 0.8578 -- Allocated Loan Amount Gross-up

DECLARE @PMT    decimal(38,2)
DECLARE @FV     decimal(38,2)
DECLARE @IPMT   decimal(38,2)
DECLARE @PPMT   decimal(38,2)

Update MA
Set MA.[Allocated Loan Amount @ Sep-13] = Convert(decimal(38,0),dbo.IPMT(@APR/12.0, @CurrentPeriod, @FullTerm, @LoanValue, 0, 0)/(@APR/12))
From dbo.TblMaster MA


Which works fine with static values but i want to change the @LoanValue to not be a static value of -20560 but to be the value from


Select MA.Loanvalue from dbo.tblmaster where MA.Serial = 'Serial from update query'


The issue i am having is that the Update query will update of every row within my table but how to do tell the @loanvalue to be the MA.loanvalue that matches the right serial number.


E.g

Serial - LoanValue - [Allocated Loan Amount @ Sep-13]
1 -2000 Convert(decimal(38,0),dbo.IPMT(@APR/12.0, @CurrentPeriod, @FullTerm, -2000, 0, 0)/(@APR/12))
2 -5000 Convert(decimal(38,0),dbo.IPMT(@APR/12.0, @CurrentPeriod, @FullTerm, -5000, 0, 0)/(@APR/12))
3 -3000 Convert(decimal(38,0),dbo.IPMT(@APR/12.0, @CurrentPeriod, @FullTerm, -3000, 0, 0)/(@APR/12))
4 -4000 Convert(decimal(38,0),dbo.IPMT(@APR/12.0, @CurrentPeriod, @FullTerm, -4000, 0, 0)/(@APR/12))


Please help

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 06/20/2013 :  11:16:48  Show Profile  Reply with Quote
Do you mean dynamically setting up @loanValue, like this?

SET @LoanValue = -20560 (Select MA.Loanvalue from dbo.tblmaster where MA.Serial = 'Serial from update query') -- Note that the result of Select statment should be one row/value .. else it will throw error

Cheers
MIK
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000