January 12, 2010 at 8:45 am
I have a table that tracks the drive space used for several servers over time. It looks something like this;
ID HostName DriveLetter DateTime DriveSize UsedSpace FreeSpace
1 BigServer1 C 5/22/2009 12:18:48 PM 20 10 10
2 BigServer1 D 5/22/2009 12:18:48 PM 100 50 50
3 BigServer1 E 5/22/2009 12:18:48 PM 300 125 175
4 BigServer1 F 5/22/2009 12:18:48 PM 500 120 380
5 BigServer1 G 5/22/2009 12:18:48 PM 50 40 10
6 BigServer1 H 5/22/2009 12:18:48 PM 80 60 20
7 BigServer1 I 5/22/2009 12:18:48 PM 20 7 13
8 BigServer1 C 5/22/2009 12:18:48 PM 20 10 10
9 BigServer1 D 5/22/2009 12:18:48 PM 100 50 50
10 BigServer1 E 5/22/2009 12:18:48 PM 300 125 175
11 BigServer1 F 5/22/2009 12:18:48 PM 500 120 380
12 BigServer1 G 5/22/2009 12:18:48 PM 50 40 10
13 BigServer1 H 5/22/2009 12:18:48 PM 80 60 20
14 BigServer1 I 5/22/2009 12:18:48 PM 20 7 13
I'm trying to come up with the sql that will give me the average disk used per day for the last 90 days. The disk used in the day could in theory be negative. I have data every 10 minutes or so for the last six months. I really just need it to work for a particular server and I'll change the select for each server. I would like to return the amount used for each day and then the average of these 90 values. Any help or pointers would be greatly appreciated.
Thanks!
January 12, 2010 at 9:46 am
First, I took your supplied data and put it into a format that is readily consumable by all of us volunteers here (take a look at the first link in my signature for how to do all of this).
Note that I changed the date on the second set of data... you should supply enough data to cover what you want to accomplish.
CREATE TABLE #Test (ID int,
HostName sysname,
DriveLetter char(1),
[Date] DateTime,
DriveSize smallint,
UsedSpace smallint,
FreeSpace smallint)
INSERT INTO #Test
SELECT 1, 'BigServer1', 'C', '5/22/2009 12:18:48 PM', 20, 10, 10 UNION ALL
SELECT 2, 'BigServer1', 'D', '5/22/2009 12:18:48 PM', 100, 50, 50 UNION ALL
SELECT 3, 'BigServer1', 'E', '5/22/2009 12:18:48 PM', 300, 125, 175 UNION ALL
SELECT 4, 'BigServer1', 'F', '5/22/2009 12:18:48 PM', 500, 120, 380 UNION ALL
SELECT 5, 'BigServer1', 'G', '5/22/2009 12:18:48 PM', 50, 40, 10 UNION ALL
SELECT 6, 'BigServer1', 'H', '5/22/2009 12:18:48 PM', 80, 60, 20 UNION ALL
SELECT 7, 'BigServer1', 'I', '5/22/2009 12:18:48 PM', 20, 7, 13 UNION ALL
SELECT 8, 'BigServer1', 'C', '5/23/2009 12:18:48 PM', 20, 10, 10 UNION ALL
SELECT 9, 'BigServer1', 'D', '5/23/2009 12:18:48 PM', 100, 50, 50 UNION ALL
SELECT 10, 'BigServer1', 'E', '5/23/2009 12:18:48 PM', 300, 125, 175 UNION ALL
SELECT 11, 'BigServer1', 'F', '5/23/2009 12:18:48 PM', 500, 120, 380 UNION ALL
SELECT 12, 'BigServer1', 'G', '5/23/2009 12:18:48 PM', 50, 40, 10 UNION ALL
SELECT 13, 'BigServer1', 'H', '5/23/2009 12:18:48 PM', 80, 60, 20 UNION ALL
SELECT 14, 'BigServer1', 'I', '5/23/2009 12:18:48 PM', 20, 7, 13
Next, the code to do the averaging for you.
--Declare and set the date range variables
DECLARE @StartDate DateTime,
@EndDate DateTime
SELECT @StartDate = '20090522', @EndDate = '20090524'
;WITH CTE AS
(
-- Get the necessary data. Remove the time from the dates
SELECT HostName,
DriveLetter,
UsedSpace,
DateOnly = DateAdd(dd, DateDiff(dd, 0, [Date]), 0)
FROM #Test
WHERE [Date] >= @StartDate
AND [Date] < @EndDate
)
-- Average the data
SELECT HostName, DriveLetter, [Date] = DateOnly, [Average] = AVG(UsedSpace)
FROM CTE
GROUP BY HostName, DriveLetter, DateOnly
ORDER BY HostName, DateOnly, DriveLetter
-- Finally, clean up
DROP TABLE #Test
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 12, 2010 at 10:52 am
Wow Wayne Thanks,
Another reason to love this forum and its many contributors. This gives me a lot to work with - thanks for your time.
I really need to take a transact sql course.
PS - Thanks for the link in your sig, I'll get it right next time!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply