March 29, 2019 at 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 tableCREATE 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
Syed_SQLDBA - Friday, March 29, 2019 10:51 AMI 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
April 1, 2019 at 6:53 am
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