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.
SQL Percent Change
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
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
Do more with
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.
from a t1 join a t2 on t1.database_name = t2.database_name
where t1.rown = 1a
and t2.rown = 2
(
[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.
from a t1 join a t2 on t1.database_name = t2.database_name
where t1.rown = 1a
and t2.rown = 2
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.
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
)
....
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.
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial