February 18, 2010 at 8:12 am
I have a table in the following format
IF OBJECT_ID(N'tempdb..#DriveInfo') IS NULL
BEGIN
CREATE TABLE #DriveInfo(
[pkDriveRecordID] [int] NOT NULL,
[fkServerID] [int] NOT NULL,
[DriveLetter] [char](1) NOT NULL,
[CapacityMB] [bigint] NOT NULL,
[UsedSpaceMB] [bigint] NOT NULL,
[FreeSpaceMB] [bigint] NOT NULL,
[FreeSpacePercent] [tinyint] NOT NULL,
[ClusterSizeKB] [smallint] NULL,
[FileFragmentationPercent] [tinyint] NULL,
[TotalFragmentationPercent] [tinyint] NULL,
[FreeSpaceFragmentationPercent] [tinyint] NULL,
[FileFragmentCount] [bigint] NULL,
[ExcessFolderFragmentCount] [bigint] NULL,
[EntryDateTime] [datetime] NOT NULL
)
END
GO
INSERT INTO #DriveInfo
SELECT 1, 3, N'C', 14324, 11126, 3198, 22, 4096, 43, 22, 1, 63988, 2150, CAST(0x00009D1401149A41 AS DateTime)
UNION
SELECT 2, 4, N'C', 14324, 11652, 2672, 19, 4096, 48, 25, 2, 56794, 1798, CAST(0x00009D1401149A41 AS DateTime)
I need to be able to retrieve the most recent record fom this table for a given pkServerID and DriveLetter and return the following table format
IF OBJECT_ID(N'tempdb..#DriveSpace') IS NULL
BEGIN
Create Table #DriveSpace(
Category varchar(20),
SizeInMB bigint
)
END
GO
INSERT INTO #DriveSpace
SELECT 'FreeSpace', 3198
UNION
SELECT 'UsedSpace', 11126
The sql I'm currently using is as follows
-- Used for comparison between desired format and query
SELECT * FROM #DriveSpace
SELECT 'FreeSpace' Measurement, D.FreeSpaceMB SizeInMB
FROM #DriveInfo D
WHERE D.fkServerID = 3 AND D.DriveLetter = 'C' AND D.EntryDateTime =
(
SELECT Max(EntryDateTime)
FROM #DriveInfo
WHERE D.fkServerID = fkServerID AND D.DriveLetter = DriveLetter
)
UNION
SELECT 'UsedSpace' Measurement, D.UsedSpaceMB SizeInMB
FROM #DriveInfo D
WHERE D.fkServerID = 3 AND D.DriveLetter = 'C' AND D.EntryDateTime =
(
SELECT Max(EntryDateTime)
FROM #DriveInfo
WHERE D.fkServerID = fkServerID AND D.DriveLetter = DriveLetter
)
Although the code above works, I am wondering if there is perhaps a more efficient query I could use. This table will end up quite large over time and I would rather be efficient now than have to come back to it later once it starts to slow down.
Many Thanks,
Paul
February 18, 2010 at 9:22 am
I am not sure what you are trying to do. If you want the latest information for each drive then try something like:
SELECT fkServerID, DriveLetter, FreeSpaceMB, UsedSpaceMB
FROM
(
SELECT fkServerID, DriveLetter, FreeSpaceMB, UsedSpaceMB
,ROW_NUMBER() OVER (PARTITION BY fkServerID, DriveLetter ORDER BY EntryDateTime DESC) AS RowNum
FROM #DriveInfo
) D
WHERE RowNum = 1
February 18, 2010 at 9:29 am
On looking at it again, maybe the following:
SELECT
CASE
WHEN N.N = 1
THEN 'FreeSpace'
ELSE 'UsedSpace'
END AS Measurement
,CASE
WHEN N.N = 1
THEN FreeSpaceMB
ELSE UsedSpaceMB
END AS SizeInMB
FROM
(
SELECT FreeSpaceMB, UsedSpaceMB
,ROW_NUMBER() OVER (ORDER BY EntryDateTime DESC) AS RowNum
FROM #DriveInfo
WHERE fkServerID = 3 AND DriveLetter = 'C'
) D
CROSS JOIN
(
SELECT 1 UNION ALL
SELECT 2
) N(N)
WHERE RowNum = 1
February 18, 2010 at 10:12 am
The second query is perfect. Thank you very much!
Just goes to show how much I still need to learn about T-SQL 🙂
Cheers,
Paul.
February 20, 2010 at 4:53 am
Even 'perfection' may be improved, perhaps...
WITH Data
AS (
-- This does the hard work of
-- getting the data
SELECT TOP (1)
UsedSpaceMB,
FreeSpaceMB
FROM #DriveInfo
WHERE fkServerID = 3
AND DriveLetter = 'C'
ORDER BY
EntryDateTime DESC
)
-- This just turns the columns into rows
SELECT Result.Measurement, Result.SizeInMB
FROM Data
UNPIVOT (
SizeInMB
FOR Measurement IN
([UsedSpaceMB], [FreeSpaceMB])
) AS Result;
🙂 😎 🙂
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 20, 2010 at 12:55 pm
SELECT TOP (1) WITH TIES add to code of Paul White
Because can be more than one record
I Have Nine Lives You Have One Only
THINK!
February 20, 2010 at 10:24 pm
handkot (2/20/2010)
SELECT TOP (1) WITH TIESadd to code of Paul WhiteBecause can be more than one record
I doubt you'd want more than one record returned in this scenario - and the test data supplied did not include any duplicates.
That said, it is generally good practice to include a tie-breaker column when looking for one result, so perhaps a better solution would be to change the ORDER BY to EntryDateTime DESC, pkDriveRecordID DESC.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 22, 2010 at 8:43 am
Thanks for all of your help guys. It's much appreciated. 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply