calculating change and percentage change

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

General SQL Server Forums

New to SQL Server Programming

calculating change and percentage change

Author

Topic

sqlchiq
Posting Yak Master

133 Posts

Posted - 2011-11-01 : 16:08:42

Hi, I have a table called 'close' with 2 columns, price and date. The price is updated once a day

I want to insert into another table called 'live', the change and percentage change from the current price and the most recent price listed in the 'close' table. The 'live' table has other columns that are populated via variables.

Not sure how to do this.


INSERT INTO live (rate, CHANGE, PERCENT_CHANGE rate_datetime, insert_datetime, symbol, source)
VALUES (66666, (PRICENOW-PRICEFROMOTHERTABLE), (((PRICENOW-PRICEFROMOTHERTABLE)/PRICEFROMOTHERTABLE)*100),NOW(), NOW(), 'XAU', 'java')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 05:52:41

[code]
INSERT INTO live (rate, CHANGE, PERCENT_CHANGE rate_datetime, insert_datetime, symbol, source)
SELECT 66666, (PRICENOW-PRICEFROMOTHERTABLE), (((PRICENOW-PRICEFROMOTHERTABLE)/PRICEFROMOTHERTABLE)*100),NOW(), NOW(), 'XAU', 'java'
from close
[/code]

i'm not sure you're using sql server as i can see NOW(). i dont think its a sql server function

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

sqlchiq
Posting Yak Master

133 Posts

Posted - 2011-11-02 : 10:06:09

i'm using mysql but i assume most of the coding are the same?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 10:35:05

quote:
Originally posted by sqlchiq

i'm using mysql but i assume most of the coding are the same?

not exactly same. I know very little on mysql so you can try given suggestion and if its not working try in mysql forum. this is ms sql server forum

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

sqlchiq
Posting Yak Master

133 Posts

Posted - 2011-11-02 : 11:56:43

this seemed to work

select rate, rate-(select price from goldclose as t2 order by dayid desc limit 1) as change1, (rate-(select price from goldclose as t2
order by dayid desc limit 1))/(select price from goldclose as t2
order by dayid desc limit 1)*100 as percent_change, rate_datetime, insert_datetime, symbol, source from gold as t1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 13:27:28

quote:
Originally posted by sqlchiq

this seemed to work

select rate, rate-(select price from goldclose as t2 order by dayid desc limit 1) as change1, (rate-(select price from goldclose as t2
order by dayid desc limit 1))/(select price from goldclose as t2
order by dayid desc limit 1)*100 as percent_change, rate_datetime, insert_datetime, symbol, source from gold as t1

ok...good for you

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources