August 27, 2012 at 10:35 am
I have a table that is storing the data file information. I need to pull out of that table the SUM of file_sizes located on each drive by server by the MAX datetime the data was collected. I have the below but is returning to big of number for filesize, looks to be adding more then just the max(date)
SELECT servername, drive_letter, SUM(file_size)/1024, MAX(date_collected) as date
FROM Imported_DBFilestest
where ServerName like 'sqlserver2%' --Limited for testing
GROUP BY servername, Drive_letter
So basically this should give me the servername, drive, the MAX date_collected, and Sum of the file sizes on each drive. But dont think the MAX is working and is Summing more then the MAX date colleted..
Any help?
Thanks,
J
August 27, 2012 at 10:52 am
Jeff Sims-413169 (8/27/2012)
I have a table that is storing the data file information. I need to pull out of that table the SUM of file_sizes located on each drive by server by the MAX datetime the data was collected. I have the below but is returning to big of number for filesize, looks to be adding more then just the max(date)SELECT servername, drive_letter, SUM(file_size)/1024, MAX(date_collected) as date
FROM Imported_DBFilestest
where ServerName like 'sqlserver2%' --Limited for testing
GROUP BY servername, Drive_letter
So basically this should give me the servername, drive, the MAX date_collected, and Sum of the file sizes on each drive. But dont think the MAX is working and is Summing more then the MAX date colleted..
Any help?
Thanks,
J
Your query appears to look reasonable based on your explanation. If you want more help than that you need to post ddl, sample data and desired output. See the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 27, 2012 at 11:20 am
Jeff Sims-413169 (8/27/2012)
I have a table that is storing the data file information. I need to pull out of that table the SUM of file_sizes located on each drive by server by the MAX datetime the data was collected. I have the below but is returning to big of number for filesize, looks to be adding more then just the max(date)
If I'm reading this right, you want the drive size as of the latest collection date. Something like this maybe?
SELECT i.servername, i.drive_letter, SUM(i.file_size)/1024, i.date_collected as date
FROM Imported_DBFilestest i
INNER JOIN (
SELECT servername, MAX(date_collected) as date_collected
FROM Imported_DBFilestest
GROUP BY servername
) x
ON i.servername = x.servername
AND i.date_collected = x.date_collected
where i.ServerName like 'sqlserver2%' --Limited for testing
GROUP BY i.servername, i.Drive_letter
_____________________________________________________________________
- Nate
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply