MS SQL Server - Perform calculation using sql script

Author

Perform calculation using sql script

Rob

2005-04-28, 8:23 pm

Is it possible to multiply a vaiable and a set column and update table with
new data??? I need to estimate future net income using a predetermined
percentage against the previous years net income numbers. I use this to run
projection calculations against estimated annual net income. The formula
would look like this:

Projected Net Income = Previous Net Income + (Previous Net Income * Percent
Increase)

Table1: INCOME
Fields: NetIncome, DataYear, ProjectedNetIncome

Table2: PROJECTION
ProjectedYr, PercentIncrease

I would like to use PercentIncrease as a variable (this number can change
year to year and give me the ability to change percentage.)

Below is what i have been trying ... i know the code is incorrect and its at
the set statement (multipling variable against netincome field).
This is logically what I want to do but I know the code is all off...
any suggestions on how to correct???

/* Declare Variables */
DECLARE @CurrentYr Numeric(9)
DECLARE @IncomeIncrease Numeric(9)
SET @CurrentYr=(SELECT ProjectedYr FROM Projections)
SET @IncomeIncrease=(SEL
ECT PercentIncrease FROM Projections)

/* Calculate Projected Income Amt for Year Entered into Projected Year Field
*/
UPDATE Income
SET ProjectedNetIncome = ((NetIncome)*((NetIn
come)*(@IncomeIncrea
se)))
WHERE DataYear = @CurrentYr

thanks in advance for any help
rob

Any suggestions would be great.

Michael C#

2005-04-28, 8:23 pm

If you could post DDL and a little sample data + your expected results, it
would help immensely. This assumes that the INCOME and PROJECTION Tables
are related via the DataYear and ProjectYear columns. If the INCOME table
has a row for DataYear = 2003, it will only update if the PROJECTION table
has a row for DataYear = 2003.

UPDATE INCOME SET ProjectedNetIncome = i.NetIncome + (i.NetIncome *
p.PercentIncrease)
FROM INCOME i, PROJECTION p
WHERE i.DataYear = p.ProjectYear

But it's hard to be certain without knowing where you're starting or where
you want to end up...

"Rob" <temp@dstek.com> wrote in message
news:eOm9mEDTFHA.3188@TK2MSFTNGP09.phx.gbl...
> Is it possible to multiply a vaiable and a set column and update table
> with
> new data??? I need to estimate future net income using a predetermined
> percentage against the previous years net income numbers. I use this to
> run
> projection calculations against estimated annual net income. The formula
> would look like this:
>
> Projected Net Income = Previous Net Income + (Previous Net Income *
> Percent
> Increase)
>
> Table1: INCOME
> Fields: NetIncome, DataYear, ProjectedNetIncome
>
> Table2: PROJECTION
> ProjectedYr, PercentIncrease
>
> I would like to use PercentIncrease as a variable (this number can change
> year to year and give me the ability to change percentage.)
>
> Below is what i have been trying ... i know the code is incorrect and its
> at
> the set statement (multipling variable against netincome field).
> This is logically what I want to do but I know the code is all off...
> any suggestions on how to correct???
>
> /* Declare Variables */
> DECLARE @CurrentYr Numeric(9)
> DECLARE @IncomeIncrease Numeric(9)
> SET @CurrentYr=(SELECT ProjectedYr FROM Projections)
> SET @IncomeIncrease=(SEL
ECT PercentIncrease FROM Projections)
>
> /* Calculate Projected Income Amt for Year Entered into Projected Year
> Field
> */
> UPDATE Income
> SET ProjectedNetIncome = ((NetIncome)*((NetIn
come)*(@IncomeIncrea
se)))
> WHERE DataYear = @CurrentYr
>
>
> thanks in advance for any help
> rob
>
> Any suggestions would be great.
>
>
>
>

Sponsored Links