Exponential Moving Avg.

Exponential Moving avg is calculated using the formula.
EMA = (Today's Price)* K + (EMA yesterday) * (1-K)
where K = 2 / (N+1)
The user is going to Input the K.

It is something like
F(N) = Price * K + F(N-1) * (1-K)

How can I reference, the previously calculated value in the Next row
calculation. I need to implement this in SQL Server.

I created a Stored procedure to do this and I used a Temp tbale with
Identity.

Create Table #TempMovAvg
(MID int identity(1,1) Primary key, tempDate DateTime, tValue float)

I Populate the data for that temp table using the below query.

Insert Into #TempMovAvg (tempDate, tValue)
Select Date, Price From DataTable

I tried Diff options to calculate the exponential Moving Avg using the
above formula, but none of them are giving the correct answers. I am
not able to reference the Prev Calculated value in the Next row
calculation.

Some queries I used.
Select a.TempDate, a.tValue,0.9*A.tValue+0.1*
(Select 0.9*t1.tValue+0.1*t2.tValue From #TempMovAvg t1, #TempMovAvg
t2 Where t1.MID=A.MID and t2.MID=t1.MID-1)
FROM #TempMovAvg A
Where A.MID>=2
order by a.TempDate

SELECT A.MId,
SUM(CASE WHEN B.MID=A.MID THEN 0.9*B.tValue
ELSE 0.1*A.tValue END) exponential_average
FROM #TempMovAvg A, #TempMovAvg B
WHERE A.MID>=2 AND A.MID BETWEEN B.MID AND B.MID+1
GROUP BY A.MID

Any help will be greatly appreciated.

thanks
Ganesh> Exponential Moving avg is calculated using the formula.
> EMA = (Today's Price)* K + (EMA yesterday) * (1-K)
> where K = 2 / (N+1)
> The user is going to Input the K.

Here is a version of the aggregate product function in SQL. You will
need to have the logorithm and exponential functions. They are not
standard, but they are very common.

The idea is that there are three special cases - all positive numbers,
one or more zeroes, and some negative numbers in the set.
You can find out what your situation is with a quick test on the
sign() of the minimum value in the set.

Within the case where you have negative numbers, there are two
sub-cases: (1) an even number of negatives or (2) an odd number of
negatives. You then need to apply some High School algebra to
determine the sign of the final result.

SELECT CASE MIN (SIGN(nbr))
WHEN 1 THEN EXP(SUM(LN(nbr))) -- all positive numbers
WHEN 0 THEN 0.00 -- some zeroes
WHEN -1 -- some negative numbers
THEN (EXP(SUM(LN(ABS(nbr))))
* (CASE WHEN
MOD (SUM(ABS(SIGN(nbr)-1)/ 2)), 2) = 1
THEN -1.00 ELSE 1.00 END)
ELSE NULL END AS big_pi
FROM NumberTable;

You will need to have the logarithm, exponential, mod and sign
functions in your SQL product. They are not standards, but they are
very common.

The idea is that there are three special cases - all positive numbers,
one or more zeros, and some negative numbers in the set. You can find
out what your situation is with a quick test on the sign() of the
minimum value in the set.

Within the case where you have negative numbers, there are two
sub-cases: (1) an even number of negatives or (2) an odd number of
negatives. You then need to apply some High School algebra to
determine the sign of the final result.

Itzak Ben-Gan had problems in implementing this in SQL Server that are
worth passing along in case your SQL product also has them. The query
as written returns a domain error in SQL Server, even though it should
not had the result expressions in the CASE expression been evaluated
<i>after<i> the conditional flow had performed a short circuit
evaluation. Examining the execution plan of the above query, it
looks like the optimizer evaluates all of the possible result
expressions in a step prior to handling the flow of the CASE
expression.

This means that in the expression after WHEN 1 ... the LN() function
is also invoked in an intermediate phase for zeros and negative
numbers, and in the expression after WHEN -1 ... the LN(ABS()) is
also invoked in an intermediate phase for 0's. This explains the
domain error.

To handle this, I had to use the ABS() and NULLIF() functions in the
positive numbers when CLAUSE, and the NULLIF() function in the
negative numbers when CLAUSE:

...
WHEN 1 THEN EXP(SUM(LN(ABS(NULLIF(result, 0.00)))))
and
...
WHEN -1
THEN EXP(SUM(LN(ABS(NULLIF(result, 0.00)))))
* CASE ...