1

What is the most efficient way to get values from 2 previous dates in one statement? (Freespace-2 and free space -3 would be 2 and 3 days prior)

SELECT 
  servername, 
  DriveName,
  DriveLabel, 
  TotalCapacity, 
  UsedCapacity, 
  FreeSpace, 
  FreeSpacePercentage, 
  '' AS [Freespace -2],
  '' AS [Frespace-3], logDate
FROM dba.DiskSpace
WHERE logDate>GETDATE()-1;


CREATE TABLE [dba].[DiskSpace](
    [servername] [varchar](100) NULL,
    [DriveName] [varchar](3) NULL,
    [DriveLabel] [varchar](100) NULL,
    [TotalCapacity] [decimal](10, 2) NULL,
    [UsedCapacity] [decimal](10, 2) NULL,
    [FreeSpace] [decimal](10, 2) NULL,
    [FreeSpacePercentage] [int] NULL,
    [logDate] [date] NULL
) ON [PRIMARY]
GO
0

1 Answer 1

4

something like below will help you :

WITH DiskSpaceLag AS (
  SELECT
    ds.servername,
    ds.DriveName,
    ds.DriveLabel,
    ds.TotalCapacity,
    ds.UsedCapacity,
    ds.FreeSpace,
    ds.FreeSpacePercentage,
    ds.logDate,
    LAG(ds.FreeSpace, 2) OVER (PARTITION BY ds.servername, ds.DriveName ORDER BY ds.logDate) AS [Freespace -2],
    LAG(ds.FreeSpace, 3) OVER (PARTITION BY ds.servername, ds.DriveName ORDER BY ds.logDate) AS [Freespace -3]
  FROM dba.DiskSpace ds
)
SELECT *
FROM DiskSpaceLag
WHERE logDate = GETDATE();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.