working out % table growth - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

working out % table growth

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-11-11 : 05:38:59

For the last few months I have been writing to a table :-

TableName (varchar(100))
Date (datetime)
DataSize (int)

For each table in my database,

I want to now measure the month by month growth (%) and the total growth (%) since the first date was recorded for each table.

Can anyone help me to achieve this please?

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-11-11 : 09:28:06

I've tried this with no luck so far,

SET NOCOUNT ON
CREATE TABLE #t1 ([tablename] VARCHAR(150),[maxdate] datetime,[maxsize] INT,[mindate] DATETIME, [minsize] INT)
DECLARE @growth float, @table_name nvarchar(150), @maxdatasize INT,@mindatasize INT,
@maxdate datetime, @mindate datetime

DECLARE table_cursor CURSOR FOR
SELECT DISTINCT [tableName]
FROM [TableSize]
ORDER BY [tableName]

OPEN table_cursor

FETCH NEXT FROM table_cursor
INTO @table_name

WHILE @@FETCH_STATUS = 0
BEGIN
-- Declare an inner cursor based
-- on @table_name from the outer cursor.

DECLARE maxdate_cursor CURSOR FOR
SELECT MAX([Date]), replace([dataSize],'KB','' ) AS [dataSize] --,MIN([Date])
FROM [TableSize] v
WHERE v.[tableName] = @table_name
GROUP BY [dataSize]

OPEN maxdate_cursor
FETCH NEXT FROM maxdate_cursor INTO @maxdate,@maxdatasize

IF @@FETCH_STATUS <> 0
PRINT ' <<None>>'

WHILE @@FETCH_STATUS = 0
BEGIN

-- Declare an inner cursor based
-- on @table_name from the outer cursor.

DECLARE mindate_cursor CURSOR FOR
SELECT MAX([Date]), replace([dataSize],'KB','' ) AS [dataSize] --,MIN([Date])
FROM [TableSize] vv
WHERE vv.[tableName] = @table_name
GROUP BY [dataSize]

OPEN mindate_cursor
FETCH NEXT FROM mindate_cursor INTO @mindate,@mindatasize

IF @@FETCH_STATUS <> 0
PRINT ' <<None>>'

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO #t1 VALUES
(@table_name,@maxdate,@maxdatasize,@mindate,@mindatasize)

FETCH NEXT FROM mindate_cursor INTO @mindate,@mindatasize

END

CLOSE mindate_cursor
DEALLOCATE mindate_cursor

FETCH NEXT FROM maxdate_cursor INTO @maxdate,@maxdatasize

END

CLOSE maxdate_cursor
DEALLOCATE maxdate_cursor

-- Get the next vendor.
FETCH NEXT FROM table_cursor
INTO @table_name
END
CLOSE table_cursor
DEALLOCATE table_cursor

SELECT * FROM #t1

DROP TABLE #t1

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-11-11 : 09:57:03

OK Scrap that, tried this:

SET NOCOUNT ON
CREATE TABLE #t1 ([tablename] VARCHAR(150),[maxdate] datetime,[maxsize] INT,[mindate] DATETIME, [minsize] INT)
DECLARE @growth float, @table_name nvarchar(150), @maxdatasize INT,@mindatasize INT,
@maxdate datetime, @mindate datetime

DECLARE table_cursor CURSOR FOR
SELECT DISTINCT [tableName]
FROM [TableSize]
where [dataSize] is not null and [dataSize] <> '0 KB'
ORDER BY [tableName]

OPEN table_cursor

FETCH NEXT FROM table_cursor
INTO @table_name

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @maxdate = MAX([Date]),@maxdatasize = replace([dataSize],'KB','' ) --,MIN([Date])
FROM [TableSize] v
WHERE v.[tableName] = @table_name
GROUP BY [dataSize]

SELECT @mindate = MIN([Date]), @mindatasize = replace([dataSize],'KB','' ) --,MIN([Date])
FROM [TableSize] vv
WHERE vv.[tableName] = @table_name
GROUP BY [dataSize]

INSERT INTO #t1 VALUES
(@table_name,@maxdate,@maxdatasize,@mindate,@mindatasize)

FETCH NEXT FROM table_cursor
INTO @table_name
END
CLOSE table_cursor
DEALLOCATE table_cursor

SELECT * FROM #t1

DROP TABLE #t1

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-11-11 : 10:25:19

Finally got this

SET NOCOUNT ON
CREATE TABLE #t1 ([tablename] VARCHAR(150),[maxdate] datetime,[maxsize] INT,[mindate] DATETIME, [minsize] INT)
DECLARE @growth float, @table_name nvarchar(150), @maxdatasize INT,@mindatasize INT,
@maxdate datetime, @mindate datetime

DECLARE table_cursor CURSOR FOR
SELECT DISTINCT [tableName]
FROM [TableSize]
where [dataSize] is not null and [dataSize] <> '0 KB'
ORDER BY [tableName]

OPEN table_cursor

FETCH NEXT FROM table_cursor
INTO @table_name

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @maxdate = MAX([Date])--,@maxdatasize = CAST(replace([dataSize],'KB','' ) AS INT)
FROM [TableSize] v
WHERE v.[tableName] = @table_name
--GROUP BY [dataSize]
SELECT @maxdatasize = CAST(replace([dataSize],'KB','' ) AS INT)
FROM [TableSize] v
WHERE v.[tableName] = @table_name AND [Date] = @maxdate

SELECT @mindate = MIN([Date])--, @mindatasize = CAST(replace([dataSize],'KB','' ) AS INT)
FROM [TableSize] vv
WHERE vv.[tableName] = @table_name
--GROUP BY [dataSize]
SELECT @mindatasize = CAST(replace([dataSize],'KB','' ) AS INT)
FROM [TableSize] vv
WHERE vv.[tableName] = @table_name AND [Date] = @mindate

INSERT INTO #t1 VALUES
(@table_name,@maxdate,@maxdatasize,@mindate,@mindatasize)
SET @table_name = ''
SET @maxdate = ''
SET @maxdatasize = 0
SET @mindate = ''
SET @mindatasize = 0

FETCH NEXT FROM table_cursor
INTO @table_name
END
CLOSE table_cursor
DEALLOCATE table_cursor

SELECT * FROM #t1

DROP TABLE #t1

----------

Now got to work out the percentages from the valuse in #t1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources