need some help in tweaking a query

  • 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.
  • 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?

  • Use LAG() to do that.

  • 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)
    Intro to Tally Tables and Functions

  • Got it. Thank you.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply