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.
TECHADVICEAsked:
Who is Participating?
 
David ToddConnect With a Mentor 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
 
David ToddSenior 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
 
David ToddSenior 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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
TECHADVICEAuthor Commented:
my periods are not actually one month apart.  It varies.  Can you adjust your statement to accomodate this?
0
 
David ToddSenior 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
 
TECHADVICEAuthor 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.

All Courses

From novice to tech pro — start learning today.