Posted by Erland Sommarskog on 11/04/05 00:56

(kalikoi@gmail.com) writes:
> select companyid,latestclosingprice,dailydate into #Tempone from
> backscreeningdata3 where dailydate
> between '12/3/04' and '01/05/05' order by companyid,dailydate desc

The ORDER BY is still meaningless.

> so i get the records for each companyid for a period of 23 days
> from these i've calculate returns for each companyid for the entire
> period
> as
>
> for each companyid for each day
> return=(price(previousday)/price(currentday)-1)*100

UPDATE a
SET col3 = (b.price/a.price - 1) * 100
FROM tbl a
JOIN tbl b ON a.companyid = b.companyid
AND a.date = daetadd(DAY, 1, b.date)

This is untested. Had you included the following:

o CREATE TABLE statement for the table.
o Sample data *as INSERT statements*
o The desired result given the sample.

You would have gotten a tested solution.

If this does not give exacatly the right result, play around with
1 and -1, and moving around the alias.

> BTW what do u mean by OP

Some people use "the OP" to refer to someone who have posted. I don't know
if they read it out as "the other person" or "the original poster", but
personally I find it impolite to talk about someone as OP when this
person is present - and adressing someone as OP?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]