calculate percentage change between two rows with same ID

Given the following table:
ID      PERIOD      DATA1      DATA2
A      20071001      101      43
A      20070901      108      40
A      20070801      98      38
B      20071001      211      110
B      20070901      195      108
B      20070801      178      94

How can I return the following results.
ID      PERIOD      DATA1      DATA2
A      20071001      -0.0693      0.0698
A      20070901      0.0926      0.0500
A      20070801
B      20071001      0.0758      0.0182
B      20070901      0.0872      0.1296
B      20070801

I need to return the percentage change between rows by id, unless the row is the last period for that id and then I would calculate nothing.
Who is Participating?

Senior DBACommented:
Hi,

This should findthe prior period, and do the join.
select
a.ID
, a.Period
, a.Data1
, b.Data1 as PData1
, a.Data2
, b.Data2 as PData2
from sometable a
left outer join sometable b
on a.ID = b.ID
and b.Period =
(
select max( c.period )
from sometable c
where a.ID = c.ID
and a.Period > c.Period
)

Please check, and then if necessary I can put this in the full query for you.

HTH
David
0

Senior DBACommented:
Hi,

You need a query like this:
select
a.ID
, a.Period
, a.Data1
, b.Data1 as PData1
, a.Data2
, b.Data2 as PData2
from sometable a
left outer join sometable b
on a.ID = b.ID
and a.Period = dateadd( month, 1, b.Period )

This should give
A 20071001 101 108 43 40
A 20070901 108 98 40 38
A 20070801 98 null 38 null
B 20071001 211 195 110 108
B 20070901 195 178 108 94
B 20070801 178 null 94 null

Then working out the percentage change should be relatively straight forward.

HTH
David

0

Senior DBACommented:
Hi,

select
ID
, Period
, ( Data1 - PData1 ) / PData1
, ( Data2 - PData2 ) / PData2
from
(
select
a.ID
, a.Period
, a.Data1
, b.Data1 as PData1
, a.Data2
, b.Data2 as PData2
from sometable a
left outer join sometable b
on a.ID = b.ID
and a.Period = dateadd( month, 1, b.Period )
)
where
PData1 is not null
and PData2 is not null

HTH
David
0

Author Commented:
my periods are not actually one month apart.  It varies.  Can you adjust your statement to accomodate this?
0

Senior DBACommented:
Hi,

It depends on how much it varies, it is a lot simplier if the periods are a clean month appart.

If it helps, and the periods are some time in the calendar month, then we could round it back to 1st of the month, that is easily done.

Can you post some actual data to illustrate it varying and by how much?

Regards
David
0

Author Commented:
98% of the time the months are a clean 3 months apart, days vary because of month
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.