Perform calculation using sql script

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=(SELECT PercentIncrease FROM Projections)
/* Calculate Projected Income Amt for Year Entered into Projected Year Field
*/
UPDATE Income
SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrease)))
WHERE DataYear = @.CurrentYr
thanks in advance for any help
rob
Any suggestions would be great.
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=(SELECT PercentIncrease FROM Projections)
> /* Calculate Projected Income Amt for Year Entered into Projected Year
> Field
> */
> UPDATE Income
> SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrease)))
> WHERE DataYear = @.CurrentYr
>
> thanks in advance for any help
> rob
> Any suggestions would be great.
>
>

Perform calculation using sql script

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=(SELECT PercentIncrease FROM Projections)
/* Calculate Projected Income Amt for Year Entered into Projected Year Field
*/
UPDATE Income
SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrea
se)))
WHERE DataYear = @.CurrentYr
thanks in advance for any help
rob
Any suggestions would be great.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=(SELECT PercentIncrease FROM Projections)
> /* Calculate Projected Income Amt for Year Entered into Projected Year
> Field
> */
> UPDATE Income
> SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrea
se)))
> WHERE DataYear = @.CurrentYr
>
> thanks in advance for any help
> rob
> Any suggestions would be great.
>
>

Perform calculation using sql script

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=(SELECT PercentIncrease FROM Projections)
/* Calculate Projected Income Amt for Year Entered into Projected Year Field
*/
UPDATE Income
SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrease)))
WHERE DataYear = @.CurrentYr
thanks in advance for any help
rob
Any suggestions would be great.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=(SELECT PercentIncrease FROM Projections)
> /* Calculate Projected Income Amt for Year Entered into Projected Year
> Field
> */
> UPDATE Income
> SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrease)))
> WHERE DataYear = @.CurrentYr
>
> thanks in advance for any help
> rob
> Any suggestions would be great.
>
>

perform calculation for only desired rows

hi all, i wasnt quite sure where to look to answer my particular problem so i am posting up this thread in the hopes that someone can point me in the right direction. in my report, i am showing sales figures for an area. i added a table to display sales from this year, sales from last year, and then the comparable percentage('this' divided by 'last' and then minus one). to account for some ppl who didnt have sales last year, i was able to use an IIF expression to return "N/A" in the textbox. my problem is i want the compared percentage to show for the area total, but not including ppl who were an 'N/A'. i am assuming that some sort of expression will be needed for the area total row, i.e. i want the area total to sum up this year and last year and then get the percentage, but i need to filter out the individuals who didnt have data for last year.

as an example

nsty nsly percentage

total: 15 7 X

A: 5 4 25%

B: 5 3 66%

C: 5 0 N/A

the percentage for total, X, should be (10/7)-1, with p0 as the format; right now it is summing rows A, B, and C.. how can i exclude row C from the calculation?

am i on the right track by researching filters and expressions, or is this a matter for the query

thanks in advance for any help

Hi

An expression will be able to handle this fine.
Use the IIF function to only include nsty if nsly is greater than zero.
For your total % sum use something like this:

= (Sum(IIF(Fields!nsly.value > 0,Fields!nsty.value,0))/Sum(Fields!nsly.Value)) -1

Hope this helps

Cheers
Mark
|||

thanks for the response; i think i still need to expand on the expression a little bit but at least i know how to go about it now.

Percentile Calculation

Hi All,

I have around 1000 employee records containing employee number and their salary. How do i calculate percentile on these records?

For example
How to calculate 25th Percentile on Salary of these 1000 records?

Hope I am clear with my question.

Thanks in advance.

Regards,
qALook up the "TOP n [PERCENT]" clause in Books Online.

select top 25 percent * from [YourTable]|||Hi,
Thanks for your immediate response. But you got it wrong. Its not Percentage, its PERCENTILE. If you have used MS-Excel there is a function called
PERCENTILE(Array, k)

For example if I have 10,20,30,40,50 as values
then 30th PERCENTILE of this comes as 22

Thanks
qA|||No, I don't get how the calculation is done. And Excel's Books Online (which is normally very good at describing statistical functions) gives no explanation of the algorithm at all.|||Wikipedia, while citing Excel's function as an example, appears to me to be describing behavior similiar to TOP %:

http://en.wikipedia.org/wiki/Percentile|||Hi,
The top clause in a query will return one ore more than one records. But Percentile will always return one and only one value.|||Yes, but how to calculate that value? I found this lovely quote:

"There is no universally accepted definition of a percentile."

on this website: http://cnx.rice.edu/content/m10805/latest/

Very curious. I guess what intrigues me is that I have been using Excel and doing applied business statistics and data mining for more than a decade and have not run into this, or ever used that Excel function before.

Try describing what you want to do, without referring to the Excel function.|||OK, the more I research PERCENTILE calculation, the more I understand why I've never used it. It is about the stupidest statistical measure I have ever seen. Why? Because makes a strict linear interpolation between just two data points out of the set, without any regard to the distribution of the data.

As an example, the 30th percentile of your values (10, 20, 30, 40, 50) is 22, as Excel calculates it. But the 30th percentile of the following values (0, 10, 20, 1000, 1000000) is...guess what...also 22.

I see no practical purpose for such a measure unless your goal is to mislead your audience.

Percentage Difference Calculation!

I have a table as follows:
Year Month Value %Diff
2004 Jul 200
Aug 100
etc etc
2003 Jul 300
Aug 400
etc etc
I need to be able to calculate the % difference for each month compared to
the same month for the previous year. The Previous() function is close to
what I need but not close enough. Any help would be much appreciated?If you have control over the source query, a LEFT OUTER JOIN ... ON
a.Month=b.Month and a.Year=b.Year-1 would bring back the data you need on
each row to calculate the diff. As you can see from this post and others,
my philosophy is not to jump through major coding hoops in the report if you
can easily solve it in the SQL query.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"St Matthew" <StMatthew@.discussions.microsoft.com> wrote in message
news:676C6EB0-0383-4BE6-86E5-58DFF89AA30B@.microsoft.com...
>I have a table as follows:
> Year Month Value %Diff
> 2004 Jul 200
> Aug 100
> etc etc
> 2003 Jul 300
> Aug 400
> etc etc
> I need to be able to calculate the % difference for each month compared to
> the same month for the previous year. The Previous() function is close to
> what I need but not close enough. Any help would be much appreciated?|||I should have mentioned in my SQL examples below that a and b are aliases
for the same table.
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:OZdTrAb0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> If you have control over the source query, a LEFT OUTER JOIN ... ON
> a.Month=b.Month and a.Year=b.Year-1 would bring back the data you need on
> each row to calculate the diff. As you can see from this post and others,
> my philosophy is not to jump through major coding hoops in the report if
> you can easily solve it in the SQL query.
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "St Matthew" <StMatthew@.discussions.microsoft.com> wrote in message
> news:676C6EB0-0383-4BE6-86E5-58DFF89AA30B@.microsoft.com...
>>I have a table as follows:
>> Year Month Value %Diff
>> 2004 Jul 200
>> Aug 100
>> etc etc
>> 2003 Jul 300
>> Aug 400
>> etc etc
>> I need to be able to calculate the % difference for each month compared
>> to
>> the same month for the previous year. The Previous() function is close
>> to
>> what I need but not close enough. Any help would be much appreciated?
>