February 5, 2019 at 2:23 pm
The query below works, but I also want to add the difference between the Max(totalspace) and Min(totalspace), I added this to the 1st select, but keep getting errors -
max(totalspace) - Min(totalspace) as Size Increase but its not working. Any help would be greatly appreciated.
Thanks
select distinct databasename, Totalspace, 100.0 / (maxval - minval) * ([totalspace] - minval) as GrowthPercentage
from (
select *,
min([totalspace]) over () as minval
, max([totalspace]) over () as maxval
from DatabaseSizeInfo
where databasename not like ('MSDB%')
and databasename not like ('Model%')
and databasename not like ('Mast%')
and databasename not like('temp%')
and databasename not like ('dbadmin%')
and logdate between '2019-01-01 00:00:00.000' and '2019-02-01 00:00:00.000'
and filetype = 'Data'
) tt
February 5, 2019 at 2:36 pm
The column "Totalspace" doesn't exist any more: the columns in the inner query are "minval" and "maxval".
So, something like:
select
databasename, minval, maxval,
100.0 / (maxval - minval) * ([totalspace] - minval) as GrowthPercentage
from (
select *,
min([totalspace]) over () as minval
, max([totalspace]) over () as maxval
from DatabaseSizeInfo
where databasename not like ('MSDB%')
and databasename not like ('Model%')
and databasename not like ('Mast%')
and databasename not like('temp%')
and databasename not like ('dbadmin%')
and logdate between '2019-01-01 00:00:00.000' and '2019-02-01 00:00:00.000'
and filetype = 'Data'
group by databasename
) tt
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 5, 2019 at 3:50 pm
Appreciate your help, but still got the same error when I tried to add maxval-minval to the 1st select.
I would have to add all columns to the group by in order to work
Here is the error I'm getting when using it -
Column 't.DatabaseName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I have two query that pull all the info I need but can't seem to get them to work together.
I have this query which gives me the size increase per datafile -
select Databasename, Totalspace, max(usedspace)- min(usedspace)as SizeIncrease from DatabaseSizeInfo
where databasename not like ('MSDB%')
and databasename not like ('Model%')
and databasename not like ('Mast%')
and databasename not like('temp%')
--and (select max(usedspace) <> min(usedspace)from DatabaseSizeInfo)
group by databasename, totalspace
And I have this query that give me the percentage of increase, Just can't get them all in one query.
select distinct databasename, Totalspace, 100.0 / (maxval - minval) * ([totalspace] - minval) as GrowthPercentage
from (
select *,
min([totalspace]) over () as minval
, max([totalspace]) over () as maxval
from DatabaseSizeInfo
where databasename not like ('MSDB%')
and databasename not like ('Model%')
and databasename not like ('Mast%')
and databasename not like('temp%')
and databasename not like ('dbadmin%')
and filetype = 'Data'
group by databasename, DatabaseSizeInfoID, FileType, logicalname, totalspace, usedspace, pctused, freespace, pctfree, physicalname, drive, logdate
) t
Here's the schema of the table I'm pulling info from in case that help -
CREATE TABLE [dbo].[DatabaseSizeInfo](
[DatabaseSizeInfoID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[DatabaseName] [sysname] NOT NULL,
[FileType] [varchar](10) NOT NULL,
[LogicalName] [sysname] NOT NULL,
[TotalSpace] [decimal](10, 2) NOT NULL,
[UsedSpace] [decimal](10, 2) NOT NULL,
[PctUsed] [decimal](5, 2) NOT NULL,
[FreeSpace] [decimal](10, 2) NOT NULL,
[PctFree] [decimal](5, 2) NOT NULL,
[PhysicalName] [sysname] NOT NULL,
[Drive] AS (left([PhysicalName],(1))),
[LogDate] [datetime] NOT NULL,
CONSTRAINT [PK_DatabaseSizeInfo_DatabaseSizeInfoID] PRIMARY KEY CLUSTERED
(
[DatabaseSizeInfoID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
February 7, 2019 at 10:17 am
You've changed the query. In your first note, you want the difference between max(totalspace) and min(). In the last one, you've added max(usedspace). If you want this, you need to add it to the inner query.
SELECT DISTINCT
databasename
, Totalspace
, maxused - minused
, GrowthPercentage = 100.0 / (maxval - minval) * (totalspace - minval)
FROM
(
SELECT
*
, minval = MIN(totalspace) OVER ()
, maxval = MAX(totalspace) OVER ()
, maxused = MAX(usedspace) OVER ()
, minused = MIN(UsedSpace) OVER ()
FROM DatabaseSizeInfo
WHERE
databasename NOT LIKE ('MSDB%')
AND databasename NOT LIKE ('Model%')
AND databasename NOT LIKE ('Mast%')
AND databasename NOT LIKE ('temp%')
AND databasename NOT LIKE ('dbadmin%')
AND logdate
BETWEEN '2019-01-01 00:00:00.000' AND '2019-02-01 00:00:00.000'
AND filetype = 'Data'
) AS tt;
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply