[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 875
  • Last Modified:

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.
0
TECHADVICE
Asked:
TECHADVICE
  • 4
  • 2
1 Solution
 
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
 
TECHADVICEAuthor Commented:
my periods are not actually one month apart.  It varies.  Can you adjust your statement to accomodate this?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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
 
David ToddSenior 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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now