Post reply

Max record per month

  • April 5, 2005 at 1:45 pm

    #66757

    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

    #550632

    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

    #550636

    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

    #550643

    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

    #550646

    (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

    #550651

    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