September 4, 2013 at 1:15 pm
I need some assistance with a database size report.
I have data pulled into a table that has a ReportDate, Database and Size:
2013-02-12 00:00:00.000METRICS2150.00
2013-02-21 00:00:00.000METRICS2250.00
2013-02-26 00:00:00.000METRICS2250.00
2013-03-04 00:00:00.000METRICS9050.00
2013-03-08 00:00:00.000METRICS2750.00
2013-03-24 00:00:00.000METRICS8850.00
2013-04-09 00:00:00.000METRICS11250.00
2013-04-21 00:00:00.000METRICS7850.00
I need the sql to return the size of the db for the last day of the date of each month. So the size on 2013-02-26, 2013-03-24 and 2013-04-21.
Note that I can't just use the last day of each month because some months don't have it....
Also I would like the query to report the month as column headers so it would look like this:
DatabaseNameJanFebMarApr
METRICS0225088507850
Thank you in advance for any assistance.
September 4, 2013 at 1:21 pm
You will probably need a tally table for this as the main table for your query. That way when a month has no data the result set will still have a row for that month. You can read about them here. http://www.sqlservercentral.com/articles/62867/[/url]
If you need further help then I would kindly ask that you take a few minutes and read the first article 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/
September 4, 2013 at 2:11 pm
SELECT
[Database],
MAX(CASE WHEN MONTH(ReportDate) = 01 THEN Size END) AS Jan,
MAX(CASE WHEN MONTH(ReportDate) = 02 THEN Size END) AS Feb,
MAX(CASE WHEN MONTH(ReportDate) = 03 THEN Size END) AS Mar,
MAX(CASE WHEN MONTH(ReportDate) = 04 THEN Size END) AS Apr,
MAX(CASE WHEN MONTH(ReportDate) = 05 THEN Size END) AS May,
MAX(CASE WHEN MONTH(ReportDate) = 06 THEN Size END) AS Jun,
MAX(CASE WHEN MONTH(ReportDate) = 07 THEN Size END) AS Jul,
MAX(CASE WHEN MONTH(ReportDate) = 08 THEN Size END) AS Aug,
MAX(CASE WHEN MONTH(ReportDate) = 09 THEN Size END) AS Sep,
MAX(CASE WHEN MONTH(ReportDate) = 10 THEN Size END) AS Oct,
MAX(CASE WHEN MONTH(ReportDate) = 11 THEN Size END) AS Nov,
MAX(CASE WHEN MONTH(ReportDate) = 12 THEN Size END) AS Dec
FROM (
SELECT
[Database],
Size,
ReportDate,
ROW_NUMBER() OVER (PARTITION BY [Database], DATEDIFF(MONTH, 0, ReportDate) ORDER BY ReportDate DESC) AS row_num
FROM dbo.tablename
) AS derived
WHERE
row_num = 1
GROUP BY
[Database]
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 6, 2013 at 12:16 pm
This works great.
I'm going to add a growth calculation column but this is exactly what I was looking for.
Thank You.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply