Percentage change between rows

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

Percentage change between rows

Author

Topic

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-09-30 : 14:18:56

Hi,

I have a table where I have a price column. I am trying to calculate the percentage change of the price from row to row. Anybody any idea how to do this?

Many thanks in advance!!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-30 : 15:10:31

One way is do a self join. One query using 2 instances of the same table in the FROM clause. You need something in the table to define the sequence of rows. Here is an example:


declare @t table (tranDate datetime, price money)
insert @t
select '2008-1-1 10:20:02.000', 10 union all
select '2008-1-1 10:20:03.000', 11 union all
select '2008-1-2 00:00:00.000', 9 union all
select '2008-1-5 00:00:00.000', 9.23 

;with seq (tranDate, price, seq)
as
(
select tranDate
       ,price
       ,row_number() over (order by tranDate)
from   @t
)
select curr.tranDate
       ,curr.price
       ,(curr.price-prev.price) / prev.price as pctChange
from   seq curr
left outer join seq prev
       on prev.seq+1 = curr.seq

output:
tranDate                price                 pctChange
----------------------- --------------------- ---------------------
2008-01-01 10:20:02.000 10.00                 NULL
2008-01-01 10:20:03.000 11.00                 0.10
2008-01-02 00:00:00.000 9.00                  -0.1818
2008-01-05 00:00:00.000 9.23                  0.0255

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 00:33:06

ANd if you're using sql 2000:-

SELECT IDENTITY(int,1,1) AS Seq,
trandate,
price
INTO #Temp
FROm YourTable
ORDER BY trandate

SELECT t1.trandate,t1.price,
(t1.price-COALESCE(t2.price,0)*100.0)/NULLIF(t2.price,0) AS increasepercent
FROM #Temp t1
LEFT JOIN #Temp t2
ON t2.Seq=t1.Seq-1

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-10-01 : 07:03:52

Hi and many thanks for the answers. I was using the latter solution, but I get wrong values over and over, eg

Time Price IncreasePercent
1 43.75 -99.0
2 43.880000000000003 -98.997028571428572
3 43.93 -98.998860528714658
4 44.0 -98.998406555884358
5 44.0 -99.0

I was trying to change the calculation (it's got to be something like [(b.price/a.price) -1]), since I think the bug is somewhere there, but nothing gets me the right solution.

anybody an idea??

many tnx again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 07:12:31

quote:
Originally posted by rocco2008

Hi and many thanks for the answers. I was using the latter solution, but I get wrong values over and over, eg

Time Price IncreasePercent
1 43.75 -99.0
2 43.880000000000003 -98.997028571428572
3 43.93 -98.998860528714658
4 44.0 -98.998406555884358
5 44.0 -99.0

I was trying to change the calculation (it's got to be something like [(b.price/a.price) -1]), since I think the bug is somewhere there, but nothing gets me the right solution.

anybody an idea??

many tnx again!

what i've used is
(currprice-prevprice)* 100.0/prevprice
can you post some sample data which you used (to get the ouput you posted). i'll try for that.

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-10-01 : 07:24:06

That should be right..

Sure, here's the data (at time 0, the increasepercentage value was NULL):

0_____43.75
1_____43.75
2_____43.88
3_____43.93
4_____44.00
5_____44.00
6_____44.03
7_____43.93
8_____43.93

thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 07:35:36

it was a matter of missing braces

SELECT IDENTITY(int,1,1) AS Seq,
trandate,
price
INTO #Temp
FROm YourTable
ORDER BY trandate

SELECT t1.trandate,t1.price,
((t1.price-COALESCE(t2.price,0))*100.0)/NULLIF(t2.price,0) AS increasepercent
FROM #Temp t1
LEFT JOIN #Temp t2
ON t2.Seq=t1.Seq-1

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-10-01 : 07:40:18

you rule!

tnx so much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 07:42:28

quote:
Originally posted by rocco2008

you rule!

tnx so much!

welcome

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-01 : 08:21:49

Be careful using SELECT IDENTITY(int,1,1)...INTO ...ORDER BY
The identity sequence is not garuanteed to be represented by the ORDER BY.

In this case it is better to create the #temp table first then do a INSERT INTO #temp...SELECT...ORDER BY

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 08:32:45

quote:
Originally posted by TG

Be careful using SELECT IDENTITY(int,1,1)...INTO ...ORDER BY
The identity sequence is not garuanteed to be represented by the ORDER BY.

In this case it is better to create the #temp table first then do a INSERT INTO #temp...SELECT...ORDER BY

Be One with the Optimizer
TG

but its working fine for me

declare @t table (tranDate datetime, price money)
insert @t
select '2008-1-1 10:20:02.000', 10 union all
select '2008-1-1 10:20:03.000', 11 union all
select '2008-1-2 00:00:00.000', 9 union all
select '2008-1-5 00:00:00.000', 9.23 union all
select '2008-3-1 10:20:03.000', 21 union all
select '2008-02-20 00:00:00.000', 19.01 union all
select '2006-1-5 00:00:00.000', 19.23 union all
select '2006-11-10 10:20:03.000', 41 union all
select '2008-10-22 00:00:00.000', 90 union all
select '2008-1-15 00:00:00.000', 91.23 union all
select '2008-8-12 10:20:03.000', 110 union all
select '2008-6-2 00:00:00.000', 91 union all
select '2008-6-5 00:00:00.000', 23


SELECT IDENTITY(int,1,1) AS Seq,trandate,price
INTO #Temp
FROM @t
ORDER BY trandate

SELECT * FROM #temp ORDER BY Seq 

drop table #Temp

output
----------------------------------------
Seq	trandate	price
1	2006-01-05 00:00:00.000	19.23
2	2006-11-10 10:20:03.000	41.00
3	2008-01-01 10:20:02.000	10.00
4	2008-01-01 10:20:03.000	11.00
5	2008-01-02 00:00:00.000	9.00
6	2008-01-05 00:00:00.000	9.23
7	2008-01-15 00:00:00.000	91.23
8	2008-02-20 00:00:00.000	19.01
9	2008-03-01 10:20:03.000	21.00
10	2008-06-02 00:00:00.000	91.00
11	2008-06-05 00:00:00.000	23.00
12	2008-08-12 10:20:03.000	110.00
13	2008-10-22 00:00:00.000	90.00

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-01 : 08:52:53

>>but its working fine for me
I didn't say it never works, I said the sequence of the identity isn't GARUANTEED to match the order by.

This topic has an example:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93431

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-01 : 08:58:00

found a KB on it:
http://support.microsoft.com/kb/273586

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources