April 5, 2005 at 1:45 pm
Stuck or tired, I can't figure out which.
Say I have 300 servers with a C & D drives I have been recording available space on the servers for months but not every day of the month. What I need to report on is as follows
For every server list their last recorded size for every month.
Example
SERVERID UPDATED Drive FREE SPACE
1 2004-02-09 11:59:03.343 C: 561565696
1 2005-03-04 08:47:11.810 C: 426283008
1 2004-02-09 11:59:03.343 D: 691609600
1 2005-03-04 08:47:11.827 D: 5262110720
5 2004-02-09 11:59:08.110 C: 116789248
5 2005-03-28 07:20:11.530 C: 82395136
5 2004-02-09 11:59:08.110 D: 1133264896
5 2005-03-28 07:20:11.543 D: 905674752
Any sugestions would be great
Stacey W. A. Gregerson
April 5, 2005 at 1:49 pm
never did this but maybe something like that could do it
Select SERVERID, UPDATED, Drive, FREESPACE from dbo.YourTable
GROUP BY
SERVERID, DRIVE, FREESPACE
HAVING UPDATED = MAX(UPDATED)
April 5, 2005 at 1:59 pm
My previous query cannot work... this one is tested and does what you ask :
Select Y2.SERVERID, Y2.UPDATED, Y2.Drive, Y1.FreeSpace from dbo.YourTable Y1 inner join
(Select SERVERID, MAX(UPDATED) as UPDATED, Drive from dbo.YourTable
GROUP BY
SERVERID, DRIVE) Y2
on Y1.SERVERID = Y2.SERVERID and Y1.Updated = Y2.Updated and Y1.Drive = Y2.Drive
ORDER BY Y1.SERVERID, Y1.DRIVE
April 5, 2005 at 2:12 pm
Not following this:
Hhere is Y2 defined? In the inner join? Write very slowly 2 hours sleep in the last 2 days. (Darn shoulder thats the last time I toose and catch a 10 year old!)
Thanks for your help.
Stacey W. A. Gregerson
April 5, 2005 at 2:16 pm
(Select SERVERID, MAX(UPDATED) as UPDATED, Drive from dbo.YourTable
GROUP BY
SERVERID, DRIVE) Y2
is a derived table, I basically select the last update date for each serverid/drive combinaison. Then I join that table to the current data to fetch the free space info.
Y2 is the name of the derived table. It's the same as doing Select O.name, O.id from dbo.SysObjects O
April 5, 2005 at 2:53 pm
Thanks. I am trying it as I type.
Stacey W. A. Gregerson
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply