Difference in value from yesterday

  • Syed_SQLDBA

    SSCertifiable

    Points: 6548

    I have a small table which collects disk size information and insert the data into a table. Now I see the disk size has changed from what it was yesterday to today. I am trying to figure out how to get the difference. For example, if the available GB yesterday was 16 and today is 14 so I want this new column to hold a difference value which is 2. Any help is highly appreciated.

    DDL for that table
    CREATE TABLE [dbo].[DiskSpaceinfo](
    [Volume] [CHAR](1) NULL,
    [TotalSize_GB] [INT] NULL,
    [Available_GB] [INT] NULL,
    [Timestamp] [DATETIME] NULL
    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[DiskSpaceinfo] ADD DEFAULT (GETDATE()) FOR [Timestamp]
    GO

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • DesNorton

    SSC-Insane

    Points: 23277

    Syed_SQLDBA - Friday, March 29, 2019 10:51 AM

    I have a small table which collects disk size information and insert the data into a table. Now I see the disk size has changed from what it was yesterday to today. I am trying to figure out how to get the difference. For example, if the available GB yesterday was 16 and today is 14 so I want this new column to hold a difference value which is 2. Any help is highly appreciated.

    DDL for that table
    CREATE TABLE [dbo].[DiskSpaceinfo](
    [Volume] [CHAR](1) NULL,
    [TotalSize_GB] [INT] NULL,
    [Available_GB] [INT] NULL,
    [Timestamp] [DATETIME] NULL
    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[DiskSpaceinfo] ADD DEFAULT (GETDATE()) FOR [Timestamp]
    GO

    Take a look at the LAG function

  • HandyD

    Mr or Mrs. 500

    Points: 507

    Try this:

    SELECT [Volume],

    [Timestamp],

    [TotalSize_GB],

    [Available_GB],

    LAG([Available_GB]) OVER (ORDER BY TIMESTAMP) AS [Previous_Available_GB]

    [Available_GB]-LAG([Available_GB]) OVER (ORDER BY TIMESTAMP) AS [Difference]

    FROM DiskSpaceInfo

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

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