Computation problem in a formula

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Computation problem in a formula

Author

Topic

kka_anand
Starting Member

24 Posts

Posted - 2010-08-21 : 13:39:46

Hi All,

I have a problem executing a formula. Below is the temporary table (#tmpTble_GL) where the key is a Price value.
Based on the price, have to find Gain, Loss, Avg-Gain, Avg-Loss, Diffr & Final. I have got the Final result, but it seems to be invalid.
The problem occurs when finding the Avg-Gain & Avg-Loss it seems. IF you see the temporary table (#tmpTble_GL) it shows the Final value as 78.21. But it should be 67.85.
I have problem calculating Avg-Gain & Avg-Loss based on the below formula. and have pasted the code below to finetune. Can anyone help me to sortout this problem.

####################################################################
The formula is
####################################################################

First Avg-Gain = Total of Gains during prior 3 periods / 4 -- (Here the Interval is 4 - from 17-Jan to 20-Jan)

Avg-Gain = [(previous Avg-Gain * 3) + current Gain] / 4

First Avg-Loss = Total of Losses during prior 4 periods / 4

Avg-Loss = [(previous AL * 3) + current Loss] / 4

Diff = Avg-Gain / Avg-Loss

Final = 100 - (100 / ( 1 + Diffr))

####################################################################

#tmpTble_GL
===========

Date Price Gain Loss Avg-Gain Avg-Loss Diffr Final
------------------------------------------------------------------------
16-Jan 141.8
17-Jan 141.55 NULL -0.25 0.00 0.00 0.00 0.00
18-Jan 146.5 4.95 NULL 0.00 0.00 0.00 0.00
19-Jan 149.3 2.8 NULL 0.00 0.00 0.00 0.00
20-Jan 147.4 NULL -1.9 1.94 0.54 3.59 ~~78.21~~ (67.85)

Note:
------
Gain & loss are calculated by finding the price difference between current day and previous day
(i.e)
141.55 - 146.55 = +4.95 (GAIN)
141.80 - 141.55 = -0.25 (LOSS)

Avg-Gain & Avg-Loss are calculated by summing the Gain & Loss
(i.e)
(4.95 + 2.8)/4 = 1.9 (Avg-Gain)
(0.25 + 1.9)/4 = 0.54 (Avg-Loss)

SP- Code
========

set @intervals = 4

select @var_Gain = sum(Gain),@var_Loss = sum(Loss) FROM #tmpTble_GL where n <= @intervals

update ttble
set
@prev_Gain = case when n < @intervals then null
when n = @intervals then (@var_Gain) end,

@prev_Loss = case when n < @intervals then null
when n = @intervals then (@var_Loss) end,

@var_AvgGain = case when n < @intervals then ABS(@prev_Gain/@intervals)
when n = @intervals then ABS(@prev_Gain/@intervals) end,

Avg-Gain = ISNULL(@var_AvgGain,0),

@var_AvgLoss = case when n < @intervals then ABS(@prev_Loss/@intervals)
when n = @intervals then ABS(@prev_Loss/@intervals) end,

Avg-Loss = ISNULL(@var_AvgLoss,0),

@var_Diffr = Cast((@var_AvgGain) as decimal(8,2)) / Cast((@var_AvgLoss) as decimal(8,2)),

Diffr = ISNULL(@var_Diffr,0),

Final = 100 - (100/(1+(Cast((ISNULL(@var_Diffr,0)) as decimal(8,2)))))

SELECT * FROM #tmpTble_GL

Thanks in Advance..!!!!!!

Regards
Anand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-21 : 14:17:55

why you've avg values only in fourth row?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kka_anand
Starting Member

24 Posts

Posted - 2010-08-21 : 14:30:03

have written like this,
select @var_Gain = sum(Gain),@var_Loss = sum(Loss) FROM #tmpTble_GL where n <= @intervals.

that's why it is displaying in the fourth row.

Thanks
Anand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-22 : 12:11:51

so you want averages every 4th row?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kka_anand
Starting Member

24 Posts

Posted - 2010-08-22 : 13:39:16

waiting for your reply....Visakh.

I want this calculation to be fine tuned..

First Avg-Gain = Total of Gains during prior 3 periods / 4 -- (Here the Interval is 4 - from 17-Jan to 20-Jan)

Avg-Gain = [(previous Avg-Gain * 3) + current Gain] / 4

First Avg-Loss = Total of Losses during prior 4 periods / 4

Avg-Loss = [(previous AL * 3) + current Loss] / 4

over here
---------
@var_AvgGain = case when n < @intervals then ABS(@prev_Gain/@intervals)
when n = @intervals then ABS(@prev_Gain/@intervals) end,

Avg-Gain = ISNULL(@var_AvgGain,0),

@var_AvgLoss = case when n < @intervals then ABS(@prev_Loss/@intervals)
when n = @intervals then ABS(@prev_Loss/@intervals) end,

Avg-Loss = ISNULL(@var_AvgLoss,0)

Current Fina result it display is 78.21 (wrong), but it should be --> 67.85
Help me to get a correct value.

Thanks
Anand

kka_anand
Starting Member

24 Posts

Posted - 2010-08-25 : 16:17:58

Hi Visakh,

Can you tell me how to calculate Avg Gain.

First Avg-Gain = Total of Gains during prior 3 periods / 4 -- (Here the Interval is 4 - from 17-Jan to 20-Jan)
Avg-Gain = [(previous Avg-Gain * 3) + current Gain] / 4

Thanks
Anand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-26 : 11:37:20

see scenario 1 here

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources