TSQL - How to get multiple values with different dates in one select?

Question

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

Answer 1

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();