Início / dba / Perguntas / 326673
Accepted
user1910240
Asked: 2023-05-03 00:16:28 +0800 CST

TSQL - Como obter vários valores com datas diferentes em um select?

  • 772

Qual é a maneira mais eficiente de obter valores de 2 datas anteriores em uma declaração? (Espaço livre-2 e espaço livre -3 seriam 2 e 3 dias antes)

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
sql-server

1 respostas

  1. Best Answer

    algo como abaixo irá ajudá-lo:

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

relate perguntas