Solved

# calculate percentage change between two rows  with same ID

Posted on 2007-10-15
868 Views
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.
0

LVL 35

Expert Comment

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

LVL 35

Expert Comment

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 Comment

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

LVL 35

Expert Comment

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 Comment

98% of the time the months are a clean 3 months apart, days vary because of month
0

LVL 35

Accepted Solution

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

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

#### 779 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!