Database
Enthusiast

## How to calculate % Change for space

Hi Gurus

 YR MONTH DBNAME TBNAME SUM_CURRENTPERM MDIFF 2,018 1 DB1 tableA 91,464,150,016 ? 2,018 2 DB1 tableA 93,397,036,544 1,932,886,528 2,018 3 DB1 tableA 95,696,584,704 2,299,548,160 2,018 4 DB1 tableA 97,729,897,472 2,033,312,768 2,018 5 DB1 tableA 99,907,048,960 2,177,151,488 2,018 6 DB1 tableA 102,151,880,704 2,244,831,744 2,018 7 DB1 tableA 104,262,243,840 2,110,363,136 2,018 8 DB1 tableA 106,436,668,416 2,174,424,576 2,018 9 DB1 tableA 108,811,854,336 2,375,185,920 2,018 10 DB1 tableA 111,287,386,624 2,475,532,288 2,018 11 DB1 tableA 113,827,301,376 2,539,914,752

thanks

KN

Accepted Solutions
Highlighted

## 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
1 ACCEPTED SOLUTION
3 REPLIES 3

## 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
Enthusiast

## Re: How to calculate % Change for space

wanted to show space percent change each month

thanks

Kartik

Highlighted

## 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