calculation - 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 2000 Forums

Transact-SQL (2000)

calculation

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-10-09 : 06:57:56

This is a sample data in table1

sector RefDate price
pharm 22 august 2007 100.21
gap 15 august 2007 10.32
pharm 21 august 2007 99.99
pharm 9 oct 2007 100.99
pharm 2 oct 2007 98.34
pharm 8 oct 2007 96.34
...

I would like to have the result as follows:
sector RefDate price priceChangeSinceYesterday PriceChangeSinceLastWeek priceChangeSinceLastMonth
pharm 9 oct 2007 100.99 100.99-96.34 100.99-98.34 100.99-lastmonth's price value

select
sector,
RefDate,
price,
priceChangeSinceYesterday??,
priceChangeSinceLastWeek???,
priceChangeSinceLastMonth??
from
table1

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 07:10:24

Next time, PLEASE provide following code like the one below!

DECLARE	@Sample TABLE (Sector VARCHAR(8), RefDate DATETIME, Price MONEY)

INSERT	@Sample
SELECT	'pharm', '22 august 2007', 100.21 UNION ALL
SELECT	'gap', '15 august 2007', 10.32 UNION ALL
SELECT	'pharm', '21 august 2007', 99.99 UNION ALL
SELECT	'pharm', '9 oct 2007', 100.99 UNION ALL
SELECT	'pharm', '2 oct 2007', 98.34 UNION ALL
SELECT	'pharm', '8 oct 2007', 96.34

And also provide expected output together with your explanation.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 07:11:31

Price change in percent, or in absolute currency?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 07:13:08

1) Also, what is your definition of a "week"?
2) "Yesterday"... Is that relative the current record in the database, or the date the query is run?

E 12°55'05.25"
N 56°04'39.16"

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-10-09 : 08:03:06

quote:
Originally posted by Peso

1) Also, what is your definition of a "week"?
2) "Yesterday"... Is that relative the current record in the database, or the date the query is run?

E 12°55'05.25"
N 56°04'39.16"

Hi,
price is just a value. no currency.
I am basically trying to get the price differences between the value of todays date and the value which is present 7 days ago, last month, etc...
Hope this is helpful
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 08:49:04

And if several values are found for last month, how do you choose which one to calculate difference with?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources