need some help in tweaking a query

Question

Post reply

need some help in tweaking a query

bobrooney.81

SSC-Addicted

Points: 468
More actions
May 19, 2022 at 5:41 am
Go to Answer
#4035376

Hi All,
I am looking for some tsql help. I am from Oracle background. Not good at programming.
Got this query from mssqltips resources which gives me database growth in last 12 months. I am getting desired output, however want an extra output column which shows the growth in percentage.
-- query
;
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])
ORDER BY [Year],[Month])
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
order by [Year],[Month]
go
-- its gives us output as below

Attachments:
You must be logged in to view attached files.
Jeff Moden

SSC Guru

Points: 1004414
More actions
May 20, 2022 at 4:04 am
Answer
#4035977

bobrooney.81 wrote:
I want to consider "Backup Size GB" and "DeltaNormal" columns and calculate percentage. I know the formula (difference/old_or_prev_val)*100.0 but I am stuck I am stuck how to compare the previous row value.
Can anybody help?
You don't need lag for the way you did this. Consider the following. You know the "Backup Size GB" and the "DeltaNormal". I you use ...
"Backup Size GB" - "DeltaNormal", what value does that produce?
And then what happens when you use "DeltaNormal"/("Backup Size GB" - "DeltaNormal) *100.0 ?
Shifting gears a bit, if you wanted to simplify this a bit, the Pieter's suggestion of using LAG() would make this quite a bit simpler. However, there's a bigger concern here... you're grouping by month. Months don't all have the same number of days so you could show a delta that's not actually correct. You could also be missing days due to downtime or whatever. I think it would be better to just doe the GB conversions for each day and plot the daily growth on an Excel Spreadsheet with a nice trend line extended a month or two into the future.

--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)