CODEWITHSUNDEEP
sqlsql-servert-sql
choloboy7
choloboy7

Reputation: 287

Calculating daily growth using previous row value in new column - SQL Server

I have the following query below that shows size of a database and when this data was collected. I am trying to create a new column that has "average daily growth" which would be determined by the size on the previous day.

If my query captures data from multiple databases/instances how can I do this for each database? As seen in herephoto

I am trying to calculate daily growth per database using the "DB Size mb" column and would like a new column called "LastDayGrowth"

This is my query:

SELECT DISTINCT
        c.instance_name ,
        a.database_name ,
        CONVERT (DATE, SWITCHOFFSET(CAST (a.collection_time AS DATETIMEOFFSET),
                                    '+00:00')) AS 'Collection Time' ,
        'LDF mb' = ( CONVERT(DEC(15, 2), a.logsize) * 8192 / 1048576.0 ) ,
        'MDF mb' = ( CONVERT(DEC(15, 2), a.dbsize) * 8192 / 1048576.0 ) ,
        ( ( CONVERT(DEC(15, 2), a.dbsize) + CONVERT(DEC(15, 2), a.logsize) )
          * 8192 / 1048576.0 ) AS 'DB Size mb' ,
        'Reserved mb' = ( a.reservedpages * 8192 / 1048576.0 ) ,
        'Data mb' = CONVERT(DEC(15, 2), a.pages) * 8192 / 1048576.0 ,
        'Index mb' = ( a.usedpages - a.pages ) * 8192 / 1048576.0 ,
        'Unused mb' = ( ( CONVERT(DEC(15, 2), a.reservedpages)
                          - CONVERT(DEC(15, 2), a.usedpages) ) * 8192
                        / 1048576.0 ) ,
        'Unallocated mb' = ( CASE WHEN a.dbsize >= a.reservedpages
                                  THEN ( CONVERT (DEC(15, 2), a.dbsize)
                                         - CONVERT (DEC(15, 2), a.reservedpages) )
                                       * 8192 / 1048576.0
                                  ELSE 0
                             END )
FROM    MDWDB.snapshots.disk_usage a
        INNER JOIN MDWDB.core.snapshots_internal b ON a.snapshot_id = b.snapshot_id
        INNER JOIN MDWDB.core.source_info_internal c ON b.source_id = c.source_id
ORDER BY c.instance_name ,
        a.database_name ASC;

I have tried this using the "LAG" function but did not succeed. I tried it like this:

LastDayGrowth = DATEDIFF(DAY,
                         LAG(( CONVERT(DEC(15, 2), a.dbsize)
                               + CONVERT(DEC(15, 2), a.logsize) )
                             * 8192 / 1048576.0, 1, NULL) OVER ( ORDER BY a.database_name, ( ( CONVERT(DEC(15,
                                                      2), a.dbsize)
                                                      + CONVERT(DEC(15,
                                                      2), a.logsize) )
                                                      * 8192
                                                      / 1048576.0 ) ),
                         ( ( CONVERT(DEC(15, 2), a.dbsize)
                             + CONVERT(DEC(15, 2), a.logsize) ) * 8192
                           / 1048576.0 ))

Any tips, or advice would be greatly appreciated!

Upvotes: 4

Views: 2003

Answers (2)

Hogan
Hogan

Reputation: 70529

Here is how to use LAG()

'DB Size mb' - 
    LAG('DB Size mb') OVER (PARTITION BY c.instance_name, a.database_name ORDER BY a.Collection_time ASC) as DB_SIZE_CHANGE

Upvotes: 1

TTeeple
TTeeple

Reputation: 2989

Doing a self join to the table so you can have a current date and a previous date should work. The ISNULL is for the first day recorded or if there is no data for the previous day. You can change it to a case statement that handles it but you should get the general idea.

SELECT DISTINCT
        c.instance_name ,
        a.database_name ,
        CONVERT (DATE, SWITCHOFFSET(CAST (a.collection_time AS DATETIMEOFFSET),
                                    '+00:00')) AS 'Collection Time' ,
        'LDF mb' = ( CONVERT(DEC(15, 2), a.logsize) * 8192 / 1048576.0 ) ,
        'MDF mb' = ( CONVERT(DEC(15, 2), a.dbsize) * 8192 / 1048576.0 ) ,
        ( ( CONVERT(DEC(15, 2), a.dbsize) + CONVERT(DEC(15, 2), a.logsize) )
          * 8192 / 1048576.0 ) AS 'DB Size mb' ,
        'LastDayGrowth' = (( ( CONVERT(DEC(15, 2), a.dbsize) + CONVERT(DEC(15, 2), a.logsize) )
          * 8192 / 1048576.0 )) - isnull((( ( CONVERT(DEC(15, 2), d.dbsize) + CONVERT(DEC(15, 2), d.logsize) )
          * 8192 / 1048576.0 )), 0), 
        'Reserved mb' = ( a.reservedpages * 8192 / 1048576.0 ) ,
        'Data mb' = CONVERT(DEC(15, 2), a.pages) * 8192 / 1048576.0 ,
        'Index mb' = ( a.usedpages - a.pages ) * 8192 / 1048576.0 ,
        'Unused mb' = ( ( CONVERT(DEC(15, 2), a.reservedpages)
                          - CONVERT(DEC(15, 2), a.usedpages) ) * 8192
                        / 1048576.0 ) ,
        'Unallocated mb' = ( CASE WHEN a.dbsize >= a.reservedpages
                                  THEN ( CONVERT (DEC(15, 2), a.dbsize)
                                         - CONVERT (DEC(15, 2), a.reservedpages) )
                                       * 8192 / 1048576.0
                                  ELSE 0
                             END )
FROM    MDWDB.snapshots.disk_usage a
        INNER JOIN MDWDB.core.snapshots_internal b ON a.snapshot_id = b.snapshot_id
        INNER JOIN MDWDB.core.source_info_internal c ON b.source_id = c.source_id
        LEFT OUTER JOIN MDWDB.snapshots.disk_usage d 
            ON a.database_name = d.database_name 
            AND c.instance_name = d.instance_name
            AND DATEADD(day, -1, a.Collection_time) = d.Collection_time
ORDER BY c.instance_name ,
        a.database_name ASC;

EDIT: added "," where missing.

Upvotes: 1

Related Questions