How to calculate % Change for space

Database
Highlighted
KN
Enthusiast

How to calculate % Change for space

Hi Gurus

 

I would need to report %change between each month.. Can you please help?

 

YRMONTH DBNAMETBNAMESUM_CURRENTPERMMDIFF
2,0181DB1tableA91,464,150,016?
2,0182DB1tableA93,397,036,5441,932,886,528
2,0183DB1tableA95,696,584,7042,299,548,160
2,0184DB1tableA97,729,897,4722,033,312,768
2,0185DB1tableA99,907,048,9602,177,151,488
2,0186DB1tableA102,151,880,7042,244,831,744
2,0187DB1tableA104,262,243,8402,110,363,136
2,0188DB1tableA106,436,668,4162,174,424,576
2,0189DB1tableA108,811,854,3362,375,185,920
2,01810DB1tableA111,287,386,6242,475,532,288
2,01811DB1tableA113,827,301,3762,539,914,752

 

thanks

KN

3 REPLIES
Senior Apprentice

Re: How to calculate % Change for space

Hi,

 

First, which percentage change do you want?

 

Is it the percentage change compared to the new size, or the old size?

 

Having asked that, the following provides both:

SEL a.*
  ,100.00 * colmdiff / sum_currentperm AS pct_change_new_size
  ,100.00 * colmdiff / MAX(sum_currentperm) OVER(PARTITION BY databasename,TABLENAME 
                                                 ORDER BY colyear, colmonth
						 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS pct_change_old_size
FROM vt1 AS a
ORDER BY 1,2;

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
KN
Enthusiast

Re: How to calculate % Change for space

wanted to show space percent change each month

 

thanks

Kartik

Senior Apprentice

Re: How to calculate % Change for space

Hi,

 

The time period is not really what I was questioning. My question was what is your 'starting point' for percentage change.

 

As an example, assume that a table has the following sizes in month 1 and month 2.

Month 1, size = 50MB

Month 2, size = 100MB

 

Do you want your 'percentage change' calculated as:

   50% - i.e. the change is 50% of the new size (use column 'pct_change_new_size' from my sql)

or

   100% - i.e. the change is 100% of the old size (use column 'pct_change_old_size' from my sql)

 

Both are 'valid' answers, it really depends on what your exact requirement is.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com