January 7, 2010 at 10:01 pm
Hi,
Hopefully I can get some help with writing a query that some of you are probably familiar with writing. Unfortunately, I don't write code often enough to get an easy set-based result set for this situation.
In the end, I'm looking to have this as the output:
SQL Server Instance | # DB's < 100GB | # DB's 100-500GB | DB's 500GB - 1TB | DB's > 1TB
I'm looking to get a count per instance:
Instance1 | 4 | 1 | 0 | 1
Instance2 | 6 | 2 | 1 | 0
Here is my table definition:
CREATE TABLE [dbo].[DBA_DB_Summary_Information](
[pk_id] [int] IDENTITY(1,1) NOT NULL,
[instance_name] [varchar](255) NOT NULL,
[db_name] [varchar](255) NULL,
[db_size] [varchar](100) NULL,
[insert_date_time] [smalldatetime] NULL,
[db_size_mb_decimal] [decimal](10, 0) NULL,
[db_created_date] [smalldatetime] NULL,
[db_owner] [varchar](255) NULL
) ON [PRIMARY]
So far, I can't get past this because of a "mental block":
SELECT instance_name, SUM(db_size_mb_decimal)/1024 [total_size_GB]
from dbo.DBA_DB_Summary_Information
GROUP BY instance_name
A case statement won't seem to work for me because it will only return one column for size.
I have been staring at this for an hour but just can't figure out how to program it. :w00t:
Thanks for any help!
Steve
January 8, 2010 at 12:07 am
In the end, I'm looking to have this as the output:
SQL Server Instance | # DB's < 100GB | # DB's 100-500GB | DB's 500GB - 1TB | DB's > 1TB
I'm looking to get a count per instance:
Instance1 | 4 | 1 | 0 | 1
Instance2 | 6 | 2 | 1 | 0
select instance_name,
sum(case when DBRange = '100GB' then DBRangecount else 0 end)[< 100GB],
sum(case when DBRange = '100-500GB' then DBRangecount else 0 end)[100-500GB],
sum(case when DBRange = '500GB-1TB' then DBRangecount else 0 end)[500GB-1TB],
sum(case when DBRange = '> 1TB' then DBRangecount else 0 end)[> 1TB]
from (select instance_name,DBRange,count(DBRange)DBRangecount from
(SELECT instance_name,
(case when (db_size_mb_decimal/1024)< 100000 then '100GB'
when (db_size_mb_decimal/1024)between 100000 and 500000 then '100-500GB'
when (db_size_mb_decimal/1024)between 500000 and 1000000 then '500GB-1TB'
when (db_size_mb_decimal/1024)> 1000000 then '> 1TB'end) DBRange
from dbo.DBA_DB_Summary_Information
)as X
group by instance_name,DBRange
)as Y
group by instance_name
January 8, 2010 at 10:59 am
Thanks!! That is perfect.
I didn't realize you could use case statements like that to get the aggregate counts.
This is interesting, definitely something for the vault.
Thanks again!
Steve
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply