I would need to report %change between each month.. Can you please help?
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;
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)
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.