SQL Percent Change

computerstreber
computerstreber used Ask the Experts™
on
I am using the code below to get database size. The ending temp table keeps a history of each run in the same table... For example:

RecordID  Name  Date            Value
1              MyCol  07/15/2009  50
2              MyCol  07/16/2009  55
3              MyCol  07/17/2009  40
4              MyCol  07/18/2009  60

Want I want to do, is write a query that takes the two most recent entries and calculates the change percentage between the two. Using the example above, this is what I would want the end result to look like:

RecordID  Name  Date            Value  Change
4              MyCol  07/18/2009  60       50
IF OBJECT_ID('[tempdb].[dbo].[#DatabaseFiles]') IS NULL
  BEGIN
      SELECT TOP 0 * INTO [#DatabaseFiles]
      FROM sys.database_files            
 
      ALTER TABLE #DatabaseFiles
      ADD CreationDate DATETIME DEFAULT(GETDATE())
  END        
 
IF OBJECT_ID('[tempdb].[dbo].[#DatabaseFilesHistory]') IS NULL
	BEGIN
		CREATE TABLE [#DatabaseFilesHistory]
		(
			  [database_name]		[sysname]		NOT NULL
			, [database_size]		[decimal]		NOT NULL
			, [database_state]		[nvarchar](60)	    NULL
			, [database_max_size]	[decimal]		NOT NULL
			, [database_growth]		[decimal]		NOT NULL
			, [create_date]			[smalldatetime]	NOT NULL
		)
	END
 
 TRUNCATE TABLE #DatabaseFiles        
 
 EXECUTE sp_msforeachdb 'INSERT INTO #DatabaseFiles SELECT *, GETDATE() FROM [?].sys.database_files' 
 
INSERT 
  INTO	[dbo].[#DatabaseFilesHistory]
		(
			  [database_name]	
			, [database_size]	
			, [database_state]	
			, [database_max_size]
			, [database_growth]	
			, [create_date]					
		)
SELECT	  name 
		, CAST(((size * 8.00)/1024.00) AS DECIMAL(18,2))
		, state_desc
		, CAST(((max_size * 8.00)/1024.00) AS DECIMAL(18,2))
		, CAST(((growth * 8.00)/1024.00) AS DECIMAL(18,2))
		, CreationDate
  FROM	[dbo].[#DatabaseFiles]
  
SELECT	*
  FROM	[dbo].[#DatabaseFilesHistory]
 ORDER
	BY	database_name ASC, create_date DESC

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
CREATE TABLE [#DatabaseFilesHistory]
            (
                    [database_name]            [sysname]            NOT NULL
                  , [database_size]            [decimal]            NOT NULL
                  , [database_state]            [nvarchar](60)          NULL
                  , [database_max_size]      [decimal]            NOT NULL
                  , [database_growth]            [decimal]            NOT NULL
                  , [create_date]                  [smalldatetime]      NOT NULL
            )


with a as (
select *, row_number() over(partition by database_name order create_date desc) as rown
from #DatabaseFilesHistory
)

select database_name, t1.create_date, t1.database_size, (100*(t1.database_size-t2.database_size)/t2.database_size))
from a t1 join a t2 on t1.database_name = t2.database_name
where t1.rown = 1a
and t2.rown = 2

Author

Commented:
I tried to execute it, but I am getting the following errors...

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'create_date'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.

I change this "by database_name order create_date desc) " to this "by database_name order by create_date desc) " and am now getting this error.

Msg 422, Level 16, State 4, Line 11
Common table expression defined but not used.
semicolon is missing at the beginning of with expression.
....
 
;with a as (
select *, row_number() over(partition by database_name order create_date desc) as rown
from #DatabaseFilesHistory
)
....

Open in new window

11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
I tried that, but still getting the same error message:

Msg 422, Level 16, State 4, Line 11
Common table expression defined but not used.

I thing the columns need to be declared in the CTE.. i.e WITH a (column A)...  AS.. However, I can't get the syntax to work.
Give this a try:
;with CTE as (
select database_name, 
	create_date, 
	database_size, 
	row_number() over(partition by database_name order by create_date desc) as rown
from #DatabaseFilesHistory
)
select 	t1.database_name, 
	t1.create_date, 
	t1.database_size, 
	(100*(t1.database_size-t2.database_size)/t2.database_size)
from CTE t1 
inner join CTE t2 on t1.database_name = t2.database_name
where t1.rown = 1 and 
t2.rown = 2

Open in new window

Author

Commented:
ralmada solution worked

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial