Post reply

Difference in value from yesterday

  • March 29, 2019 at 10:51 am

    #2033868

    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]

  • March 29, 2019 at 11:30 am

    #2034209

    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

    How to post data/code on a forum to get the best help.

  • April 1, 2019 at 6:53 am

    #2313382

    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 2 (of 2 total)

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