February 4, 2010 at 10:20 pm
Hi,
I have some daily stats about database sizes in a table like this:
Date DbnameDBSize
1/01/2010DB1100
1/01/2010DB270
2/01/2010DB1102
2/01/2010DB271
………
8/01/2010DB1110
8/01/2010DB275
I want to get a report out of it which will show weekly database sizes and weekly increments with database names being columns:
Date DB1Incriment_DB1DB2Incriment_DB2…
8/01/201011010 755
1/01/20101000 700
The number and names of the databases is not known in advance, so the report can work on any server.
Any ideas?
Thanks.
February 5, 2010 at 8:30 am
Check out the last 2 articles in my signature. I think they will provide you with the information you need.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 5, 2010 at 8:36 am
February 5, 2010 at 9:37 pm
Ramesh, I don't believe he has to worry about that. If he first does a crosstab or pivot as Jack suggests, he can just do calculations using the columns of each row to get the increments. A little dynamic SQL magic is needed.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 6, 2010 at 1:13 am
The Dixie Flatline (2/5/2010)
Ramesh, I don't believe he has to worry about that. If he first does a crosstab or pivot as Jack suggests, he can just do calculations using the columns of each row to get the increments. A little dynamic SQL magic is needed.
I believe he has to. Because he has to compare previous and next rows to get the difference. I would be very thankful if you can show me some code that does this kind of magic.
--Ramesh
February 6, 2010 at 1:31 am
Ramesh Saive (2/6/2010)
The Dixie Flatline (2/5/2010)
Ramesh, I don't believe he has to worry about that. If he first does a crosstab or pivot as Jack suggests, he can just do calculations using the columns of each row to get the increments. A little dynamic SQL magic is needed.I believe he has to. Because he has to compare previous and next rows to get the difference. I would be very thankful if you can show me some code that does this kind of magic.
But just providing differences between prev and next doesn't usually require those techniques Ramesh, because are not running totals, they are just "standing" deltas. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 6, 2010 at 2:05 am
RBarryYoung (2/6/2010)
Ramesh Saive (2/6/2010)
The Dixie Flatline (2/5/2010)
Ramesh, I don't believe he has to worry about that. If he first does a crosstab or pivot as Jack suggests, he can just do calculations using the columns of each row to get the increments. A little dynamic SQL magic is needed.I believe he has to. Because he has to compare previous and next rows to get the difference. I would be very thankful if you can show me some code that does this kind of magic.
But just providing differences between prev and next doesn't usually require those techniques Ramesh, because are not running totals, they are just "standing" deltas. 🙂
I hope I can learn something from here, if you can show me some code that does this without using recursive CTEs and not assuming the dates are sequential and gapless.
Here is the sample script to get started:
IF ( OBJECT_ID( 'tempdb..#Databases' ) IS NOT NULL )
DROP TABLE #Databases
CREATE TABLE #Databases
(
Date SMALLDATETIME NOT NULL,
DbName VARCHAR(128) NOT NULL,
DbSize NUMERIC(10,2) NOT NULL
)
INSERT#Databases( Date, DbName, DbSize )
SELECT'01-Jan-2010', 'DB1', 100 UNION ALL
SELECT'01-Jan-2010', 'DB2', 70 UNION ALL
SELECT'02-Jan-2010', 'DB1', 102 UNION ALL
SELECT'03-Jan-2010', 'DB2', 71 UNION ALL
SELECT'08-Jan-2010', 'DB1', 110 UNION ALL
SELECT'08-Jan-2010', 'DB2', 75
SELECT* FROM #Databases
--Ramesh
February 6, 2010 at 2:50 am
Would you mind sharing what you've tried so far and where you got stuck?
Did you have a look at the articles Jack referred to?
February 6, 2010 at 3:12 am
IF ( OBJECT_ID( 'tempdb..#Databases' ) IS NOT NULL )
DROP TABLE #Databases
CREATE TABLE #Databases
(
Date SMALLDATETIME NOT NULL,
DbName VARCHAR(128) NOT NULL,
DbSize NUMERIC(10,2) NOT NULL
)
INSERT #Databases( Date, DbName, DbSize )
SELECT '01-Jan-2010', 'DB1', 100 UNION ALL
SELECT '01-Jan-2010', 'DB2', 70 UNION ALL
SELECT '02-Jan-2010', 'DB1', 102 UNION ALL
SELECT '03-Jan-2010', 'DB2', 71 UNION ALL
SELECT '08-Jan-2010', 'DB1', 110 UNION ALL
SELECT '08-Jan-2010', 'DB2', 75
;WITH CTE AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY DATE) AS ID,DATE,DB1,DB2
FROM
(
SELECT
Date,
DbName,
DbSize
FROM
#Databases
)t
PIVOT
(
MAX(DbSize) FOR DbName IN ([DB1],[DB2])
)PIVOTS
)
,CTE2 AS
(
SELECT
ID,
DATE,
DB1,
CAST( 0 AS NUMERIC) AS INCREMENTAL_DB1,
DB2,
CAST( 0 AS NUMERIC) AS INCREMENTAL_DB2,
DB1 AS CurrentDB1,
DB2 AS CurrentDB2
FROM
CTE
WHERE
ID=1
UNION ALL
SELECT
C.ID,
C.DATE,
CASE WHEN C.DB1 IS NULL THEN C2.CurrentDB1 ELSE C.DB1 END AS DB1,
CASE WHEN C.DB1 IS NULL THEN 0 ELSE CAST(C.DB1-ISNULL(C2.CurrentDB1,0) AS NUMERIC) END AS INCREMENTAL_DB1,
CASE WHEN C.DB2 IS NULL THEN C2.CurrentDB2 ELSE C.DB2 END AS DB2,
CASE WHEN C.DB2 IS NULL THEN 0 ELSE CAST(C.DB2-ISNULL(C2.CurrentDB2,0) AS NUMERIC) END AS INCREMENTAL_DB2,
CASE WHEN C.DB1 IS NULL THEN C2.CurrentDB1 ELSE C.DB1 END AS CurrentDB1,
CASE WHEN C.DB2 IS NULL THEN C2.CurrentDB2 ELSE C.DB2 END AS CurrentDB2
FROM
CTE C
INNER JOIN
CTE2C2
ON
C.ID=C2.ID+1
)
SELECT DATE,DB1,INCREMENTAL_DB1,DB2,INCREMENTAL_DB2 FROM CTE2
Regards,
Mitesh OSwal
+918698619998
February 10, 2010 at 8:39 pm
lmu92 (2/6/2010)
Would you mind sharing what you've tried so far and where you got stuck?Did you have a look at the articles Jack referred to?
Sorry I've gone missing. I've looked through the articles above. I get stuck when it comes to the dynamic number of columns in the report. In the example above, there are only two databases. In my case there may be any number.
Another thing, I want to only include every 7th record, or to be more accurate, every record which falls on Sunday for example, not all the records, so to observe how much the database grows week by week.
February 11, 2010 at 12:34 pm
Roust_m (2/10/2010)
lmu92 (2/6/2010)
Would you mind sharing what you've tried so far and where you got stuck?Did you have a look at the articles Jack referred to?
Sorry I've gone missing. I've looked through the articles above. I get stuck when it comes to the dynamic number of columns in the report. In the example above, there are only two databases. In my case there may be any number.
Another thing, I want to only include every 7th record, or to be more accurate, every record which falls on Sunday for example, not all the records, so to observe how much the database grows week by week.
You have to perform three steps, I think:
Step 1: get all data that are collected on a Sunday
Question to be answered: How would you deal with weeks where you don't have data collected on Sunday?
Step 2: Calculate the DbGrowth using a self join and Row_Number function and store it in an intermediate table
Step 3: build the dynamic query.
Everything put together could look like:
;WITH cte AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY DbName ORDER BY DATE) AS ROW,
DATE,
DbName,
DbSize
FROM #Databases
WHERE DATEDIFF(dd,'19000101',DATE)% 7 = 6
)
SELECT
cte1.date,
cte1.dbname,
cte1.dbsize,
ISNULL(cte1.dbsize-cte2.dbsize,0) AS Increment
INTO #DatabasesSum
FROM cte cte1
LEFT OUTER JOIN cte cte2
ON cte1.dbname=cte2.dbname
AND cte1.row=cte2.row+1
DECLARE @SQL1 NVARCHAR(4000),
@SQL2 NVARCHAR(4000),
@SQL3 NVARCHAR(4000)
--===== Create the "static" section of the code
SELECT @SQL1 = 'SELECT Date'+CHAR(10)
--===== Create the "Mostly Static" section of the code
SELECT @SQL3 = '
from #DatabasesSum GROUP BY Date order by Date desc'
--===== The "Hard" Part - Concatenation to Form the Date Columns
SELECT @SQL2 = COALESCE(@SQL2,'')
+ ',max(CASE WHEN dbname = ' + QUOTENAME(d.dbname,'''')
+ ' THEN dbsize ELSE -1 END) AS ' + d.dbname + '' + CHAR(10)
+ ',max(CASE WHEN dbname = ' + QUOTENAME(d.dbname,'''')
+ ' THEN Increment ELSE -1 END) AS Increment_' + d.dbname + '' + CHAR(10)
FROM
(
SELECT dbname FROM #DatabasesSum GROUP BY dbname
) d ORDER BY dbname
--===== Print the Dynamic SQL
PRINT @SQL1 + @SQL2 + @SQL3
--===== Execute the Dynamic SQL to create the desired report (uncomment to run it)
--EXEC (@SQL1 + @SQL2 + @SQL3)
DROP TABLE #DatabasesSum
February 11, 2010 at 11:19 pm
Question to be answered: How would you deal with weeks where you don't have data collected on Sunday?
Ideally I would like to take the database size and growth over two weeks and populate some sort of average for the week with missed stats. If this is too hard, then may be take Monday's or Saturday's data for the missing Sunday.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply