Calculating Returns - SQL Server Forums

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)

Calculating Returns

Author

Topic

zhuanyi
Starting Member

5 Posts

Posted - 2010-02-01 : 14:20:09

Hi,
Say if I have a table with 2 columns, first is the date and second is the price, and I would like to create a view showing the returns where:

Rate of Return = (Price at day x - Price at day (x-1)) / Price at day x

In the end, I would like to see a view with 5 columns showing x, (x-1), price at x, price at (x-1) and the return.

May I know how I can do that? Thanks a lot!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-02-01 : 14:49:23

[code]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_Returns]
/**********************************************************************************
DATE AUTHOR PURPOSE

***********************************************************************************/
AS
SELECT x,
(x-1),
price at x,
price at (x-1),
(Price at day x - Price at day (x-1)) / Price at day x
FROM table
[/code]

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

zhuanyi
Starting Member

5 Posts

Posted - 2010-02-01 : 15:27:54

Thanks a lot for your reply. The thing is there is no column called price at x, what I need is the corresponding price for the date (x-1), i.e., in Excel, if we rank by date, it is the price on row (x-1)...
Thanks!

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-02-01 : 15:30:20

Use the Row_Number() function to get (x-1)

http://msdn.microsoft.com/en-us/library/ms186734.aspx

mymatrix
Starting Member

24 Posts

Posted - 2010-02-02 : 01:12:25

Check this out:

Select Date as 'Current Date',
Price as 'Current Day Price',
'Previous Date' = (
select top 1 T1.Date from Table T1 where T1.Date=DateAdd(dd,-1,T.Date)
) ,
'Previous Day Price' = (
select top 1 T2.Price from Table T2 where T2.Date=DateAdd(dd,-1,T.Date)
) ,
'Rate Of Return' = (
select top 1 ((T.Price - T3.Price)/T.Price) from Table T3 where T3.Date=DateAdd(dd,-1,T.Date)
)
from Table T

thnks
Gaurav

Even my blood group says be -ve to all the negatives.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 05:41:46

if dates are not contiguos you can do below


SELECT t.date as x,
       t1.date as [x-1],
       t.price as pricex,
       t1.price as [pricex-1],
       (t.price-t1.price)*1.0/t.price as [return]
from table t
outer apply (select top 1 date,price
             from table
             where date < t.date
             order by date desc) t1

zhuanyi
Starting Member

5 Posts

Posted - 2010-02-02 : 07:27:45

i think visakh16's answer made the most sense, thanks a lot!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 09:32:22

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources