Hi,@Bob sql
Welcome to Microsoft T-SQL Q&A Forum!
I agree with Viorel's approach , here you need to be clear about which column to calculate the percentage , I guess you want to calculate the ratio of compressed data compared to before backup , something like this (2021-7): -598.43 /3022.37=-19%.
If you want to compare the percentage before and after compression , you can use the lag function, use
percent=(deltaCompressed-LAG(deltaCompressed)over(order by Month asc)/LAG(deltaCompressed)over(order by month asc))*100
About LAG can refer to this link . You might try this, as a hint that it is untested:
WITH BackupsSize AS(
SELECT TOP 1000
rn = ROW_NUMBER() OVER (ORDER BY DATEPART(year,[backup_start_date]) ASC, DATEPART(month,[backup_start_date]) ASC)
, [Year] = DATEPART(year,[backup_start_date])
, [Month] = DATEPART(month,[backup_start_date])
, [Month Name] = DATENAME(month,[backup_start_date])
, [Backup Size GB] = CONVERT(DECIMAL(10,2),ROUND(AVG([backup_size]/1024/1024/1024),4))
, [Compressed Backup Size GB] = CONVERT(DECIMAL(10,2),ROUND(AVG([compressed_backup_size]/1024/1024/1024),4))
FROM
msdb.dbo.backupset
WHERE
[database_name] = N'dbname'
AND [type] = 'D'
AND backup_start_date BETWEEN DATEADD(mm, - 11, GETDATE()) AND GETDATE()
GROUP BY
[database_name]
, DATEPART(yyyy,[backup_start_date])
, DATEPART(mm, [backup_start_date])
,DATENAME(month,[backup_start_date])
)
,cte1 as
(
SELECT
b.Year,
b.Month,
b.[Month Name],
b.[Backup Size GB],
0 AS deltaNormal,
b.[Compressed Backup Size GB],
0 AS deltaCompressed
FROM BackupsSize b
WHERE b.rn = 1
UNION
SELECT
b.Year,
b.Month,
b.[Month Name],
b.[Backup Size GB],
b.[Backup Size GB] - d.[Backup Size GB] AS deltaNormal,
b.[Compressed Backup Size GB],
b.[Compressed Backup Size GB] - d.[Compressed Backup Size GB] AS deltaCompressed
FROM BackupsSize b
CROSS APPLY (
SELECT bs.[Backup Size GB],bs.[Compressed Backup Size GB]
FROM BackupsSize bs
WHERE bs.rn = b.rn - 1
) AS d
),cte2 as
(
select Year,Month,[Month Name],[Backup Size GB],deltaNormal,[Compressed Backup Size GB]
, deltaCompressed,
case when deltaCompressed=0 then 0
else (deltaCompressed-LAG(deltaCompressed)over(order by Month )/LAG(deltaCompressed)over(order by month ))*100
end as growth
from cte1
)
select * from cte2
order by [Year],[Month]
Best regards,
Bert Zhou
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.